0

I have to work with data exported from SQL Server as a CSV. The CSV file contains embedded linefeed characters, for example:

Jane Doe, 123 Main Street{LF}Apt B, Anytown, NY, 10010{CR}{LF}

When the file is imported into Excel, Excel creates a new row when it encounters a linefeed:

Jane Doe|123 Main Street Apt B|Anytown|NY|10010

so that "Apt B" is now in the "Name" field of a row, etc etc.

Is there a Preferences page for Imports where I can tell Excel that the end-of-record is {CR}{LF} not {LF}?

  • 1
    Welcome to the site. Have you done any research or attempted anything yet? If so, please use the edit feature to add those details so we can see where you are stuck. – CharlieRB Jun 8 '17 at 11:49
  • absent VBA maybe the Data-> Get & Transform would work for you (or if your in older version of excel then it's a PowerQuery add in. – gns100 Jun 8 '17 at 19:40
0

Depending of your Excel Version you can use

  • in Excel 2016 the Get & Transform function
  • or previously (Excel 2010/2013) the same tool as free MS add-In called Power Query (Excel 2010/2013)

The import of a csv-file (or directly connect to many different source like SQL Server) is so very convenient, especially if you want to transform your data after the import step.

I've tried a sample like above and didn't had any issue with the line feed. Here the Code after importing with the UI. It's described on the MS Support website

let
    Source = Csv.Document(File.Contents("C:\YourFile.csv"),[Delimiter=",", Columns=5, Encoding=1252]),
    ChangeType = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    ReplaceLFcol2 = Table.ReplaceValue(ChangeType,"{LF}"," ",Replacer.ReplaceText,{"Column2"})
in
    ReplaceLFcol2 
  • Thanks for that link. I will look into it. Site won't let me upvote the answer. – mrblint Jun 11 '17 at 1:54
  • I have an online subscription to Office. My Excel doesn't seem to have the feature. At least there's no New Query option on the Data tab where the online technical documents indicate it should appear. – mrblint Jun 11 '17 at 11:46
  • And when I follow this troubleshooting advice (support.office.com/en-us/article/…), Power Query is not listed in the COM Add-ins available. I see Microsoft Power Map for Excel, some Team Foundation add-ins, and add-ins from Visual Studio tools only. – mrblint Jun 11 '17 at 12:39
  • if you use the free Office Online Version (aka 2016), then you won't have this feature. Your Office subscription has to be an Office ProPlus. Look at your data ribbon. If you have a block "get & transform" then you can use it. Power Query is only for former Excel Version available. s. also on the Support page the 2nd picture. – visu-l Jun 12 '17 at 7:00
  • It's a monthly paid subscription. I do have the Get & transform block. But it does not have a New Query icon. – mrblint Jun 13 '17 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.