SOLVED

Import CSV change

%3CLINGO-SUB%20id%3D%22lingo-sub-1561121%22%20slang%3D%22en-US%22%3EImport%20CSV%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561121%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnnoying%20new%20problem%2C%20with%20a%20recent%20Excel%20%22update%22...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20standardized%20CSV%20file%20format%20that%20used%20to%20load%20into%20excel%20fine.%3C%2FP%3E%3CP%3EThe%20file%20is%20%22%3B%22%20separated%20and%20commences%20with%20a%20few%20lines%20of%20header%20data%2C%202%20columns%20and%20then%20multiple%20lines%20of%20data%2010%2B%20columns%20of%20main%20data.%3C%2FP%3E%3CP%3EPreviously%20Excel%20used%20to%20look%20at%20file%20and%20set%20up%20the%20import%20to%20bring%20data%20in%20for%20the%2010%2B%20columns.%3C%2FP%3E%3CP%3ENow%20it%20seems%20to%20look%20at%20the%20first%20line%20and%20then%20set%20all%20import%20to%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHeader1%3B%20text1%26nbsp%3B%3C%2FP%3E%3CP%3EHeader2%3B%20text2%3C%2FP%3E%3CP%3EHeader3%3B%20text3%3C%2FP%3E%3CP%3EData1%3B%20Data2%3B%20Data3%3B%20Data4%3B%20Data5%3B%20Data6%3B%20Data7%3B%20Data8%3B%20Data9%3B%20Data10%3C%2FP%3E%3CP%3EData1%3B%20Data2%3B%20Data3%3B%20Data4%3B%20Data5%3B%20Data6%3B%20Data7%3B%20Data8%3B%20Data9%3B%20Data10%3C%2FP%3E%3CP%3EData1%3B%20Data2%3B%20Data3%3B%20Data4%3B%20Data5%3B%20Data6%3B%20Data7%3B%20Data8%3B%20Data9%3B%20Data10%3C%2FP%3E%3CP%3EData1%3B%20Data2%3B%20Data3%3B%20Data4%3B%20Data5%3B%20Data6%3B%20Data7%3B%20Data8%3B%20Data9%3B%20Data10%3C%2FP%3E%3CP%3EData1%3B%20Data2%3B%20Data3%3B%20Data4%3B%20Data5%3B%20Data6%3B%20Data7%3B%20Data8%3B%20Data9%3B%20Data10%3C%2FP%3E%3CP%3EData1%3B%20Data2%3B%20Data3%3B%20Data4%3B%20Data5%3B%20Data6%3B%20Data7%3B%20Data8%3B%20Data9%3B%20Data10%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20I%20use%20the%20previous%20default%20and%20load%20this%20all%3F%20or%26nbsp%3B%3C%2FP%3E%3CP%3ESelect%20which%20line%20sets%20the%20number%20of%20columns%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EATM%20I%20have%20to%20edit%20the%20CSV%20and%20either%20drop%20a%20dummy%20copy%20of%20a%20dataline%20into%20the%20first%20line%20or%26nbsp%3B%3C%2FP%3E%3CP%3EDelete%20my%20header%20data.%3C%2FP%3E%3CP%3ENeither%20is%20what%20I%20want%20to%20do.....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThoughts%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1561121%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1561372%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20CSV%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561372%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658269%22%20target%3D%22_blank%22%3E%40Chris_Shackleton%3C%2FA%3E%26nbsp%3B%2C%20you%20can%20use%20the%20%22From%20Text%2FCSV%22%20option%20from%20the%20Get%20%26amp%3B%20Transform%20section%20in%20the%20Data%20tab%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TheAntony_0-1596384360231.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209720iE7F850C66CCFE3AD%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TheAntony_0-1596384360231.png%22%20alt%3D%22TheAntony_0-1596384360231.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%2C%20in%20the%20dialog%20that%20pops%20up%2C%20select%20the%20%22Transform%20Data%22%20Button%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TheAntony_1-1596384427587.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209721i0A16595FE57E6DC6%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TheAntony_1-1596384427587.png%22%20alt%3D%22TheAntony_1-1596384427587.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20take%20you%20to%20the%20Power%20Query%20Editor%20and%20you%20can%20do%20a%20lot%20of%20transformations%20from%20here.%20In%20this%20case%20you%20can%20remove%20the%20top%203%20rows%20and%20perform%20further%20data%20shaping%20you%20require%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TheAntony_2-1596384557138.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209722i9E3A9629DDF39195%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TheAntony_2-1596384557138.png%22%20alt%3D%22TheAntony_2-1596384557138.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOnce%20you%20are%20done%20with%20your%20transformations%2C%20click%20on%20Home-%26gt%3BClose%20and%20Load%20To%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TheAntony_3-1596384621119.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209723i28412A7E73CD65E1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TheAntony_3-1596384621119.png%22%20alt%3D%22TheAntony_3-1596384621119.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20load%20to%20your%20desired%20location%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22TheAntony_4-1596384666878.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209724iC7713ABB5C42B9AE%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22TheAntony_4-1596384666878.png%22%20alt%3D%22TheAntony_4-1596384666878.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1561404%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20CSV%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561404%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F383224%22%20target%3D%22_blank%22%3E%40TheAntony%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20thanks%20for%20the%20response.%3C%2FP%3E%3CP%3EHadn't%20used%20that%20option%20before.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhile%20it%20does%20allow%20me%20to%20deselect%20the%20top%20rows%20when%20I%20%22delete%22%20them%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20import%20is%20still%20limited%20to%20the%202%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAm%20I%20missing%20a%20stage%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1561408%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20CSV%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561408%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658269%22%20target%3D%22_blank%22%3E%40Chris_Shackleton%3C%2FA%3E%26nbsp%3B%2C%20Power%20Query%20is%20incredibly%20powerful%20and%20can%20pretty%20much%20do%20everything%20you%20can%20throw%20at%20it.%20I%20was%20able%20to%20import%20everything%20based%20on%20a%20made%20up%20txt%20file%20I%20created%20from%20your%20original%20post.%20If%20you%20can%20share%20a%20sample%20of%20your%20file%20(removing%20any%20confidential%20data)%2C%20I%20can%20provide%20more%20assistance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1561605%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20CSV%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561605%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658269%22%20target%3D%22_blank%22%3E%40Chris_Shackleton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20general%20you%20may%20allow%20legacy%20text%20import%20wizard%20in%20options%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20766px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209815i040884BF892245FF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhich%20was%20the%20only%20option%20in%20previous%20Excel%20versions%2C%20and%20use%20it.%20But%20both%20this%20wizard%20and%20Power%20Query%20give%20exactly%20the%20same%20result%2C%20they%20recognize%20all%20data%20columns.%20Perhaps%20it's%20something%20else%20with%20your%20file%2C%20better%20if%20you%20submit%20it%20as%20txt%20file%2C%20not%20as%20the%20part%20of%20the%20text%20within%20the%20post%2C%20which%20is%20actually%20HTML.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576775%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20CSV%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576775%22%20slang%3D%22en-US%22%3EMust%20be%20something%20strange%20about%20the%20file%3CBR%20%2F%3ESee%20attached.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576777%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20CSV%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576777%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658269%22%20target%3D%22_blank%22%3E%40Chris_Shackleton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20forgot%20to%20add%20data%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576785%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20CSV%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576785%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F658269%22%20target%3D%22_blank%22%3E%40Chris_Shackleton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBoth%20Power%20Query%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20849px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211258i325FF24EB53D99AB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20legacy%20wizard%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20698px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211259i10596B3ED9008BAC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eimport%20data%20from%20this%20file%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576810%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20CSV%20change%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576810%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20quick%20response.%3C%2FP%3E%3CP%3EOK%20so%20when%20I%20load%20the%20file%20I%20sent%20(just%20edited%20to%20remove%20sensitive%20data)%20it%20also%20works%20for%20me.%3C%2FP%3E%3CP%3ESeems%20like%20the%20original%20just%20needs%20to%20be%20opened%20in%20a%20text%20editor%20and%20re-saved.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESolved%20thanks%20m8%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

 

Annoying new problem, with a recent Excel "update"...

 

I have a standardized CSV file format that used to load into excel fine.

The file is ";" separated and commences with a few lines of header data, 2 columns and then multiple lines of data 10+ columns of main data.

Previously Excel used to look at file and set up the import to bring data in for the 10+ columns.

Now it seems to look at the first line and then set all import to that.

 

Header1; text1 

Header2; text2

Header3; text3

Data1; Data2; Data3; Data4; Data5; Data6; Data7; Data8; Data9; Data10

Data1; Data2; Data3; Data4; Data5; Data6; Data7; Data8; Data9; Data10

Data1; Data2; Data3; Data4; Data5; Data6; Data7; Data8; Data9; Data10

Data1; Data2; Data3; Data4; Data5; Data6; Data7; Data8; Data9; Data10

Data1; Data2; Data3; Data4; Data5; Data6; Data7; Data8; Data9; Data10

Data1; Data2; Data3; Data4; Data5; Data6; Data7; Data8; Data9; Data10

 

How do I use the previous default and load this all? or 

Select which line sets the number of columns

 

ATM I have to edit the CSV and either drop a dummy copy of a dataline into the first line or 

Delete my header data.

Neither is what I want to do.....

 

Thoughts?

 

8 Replies
Highlighted

@Chris_Shackleton , you can use the "From Text/CSV" option from the Get & Transform section in the Data tab:

TheAntony_0-1596384360231.png

 

Then, in the dialog that pops up, select the "Transform Data" Button:

TheAntony_1-1596384427587.png

 

This will take you to the Power Query Editor and you can do a lot of transformations from here. In this case you can remove the top 3 rows and perform further data shaping you require:

TheAntony_2-1596384557138.png

 

Once you are done with your transformations, click on Home->Close and Load To:

TheAntony_3-1596384621119.png

 

You can load to your desired location:

TheAntony_4-1596384666878.png

 

 

Highlighted

@TheAntony 

Hi thanks for the response.

Hadn't used that option before.

 

While it does allow me to deselect the top rows when I "delete" them 

the import is still limited to the 2 columns.

 

Am I missing a stage?

Highlighted

@Chris_Shackleton , Power Query is incredibly powerful and can pretty much do everything you can throw at it. I was able to import everything based on a made up txt file I created from your original post. If you can share a sample of your file (removing any confidential data), I can provide more assistance.

Highlighted
Best Response confirmed by Chris_Shackleton (Occasional Contributor)
Solution

@Chris_Shackleton 

In general you may allow legacy text import wizard in options

image.png

which was the only option in previous Excel versions, and use it. But both this wizard and Power Query give exactly the same result, they recognize all data columns. Perhaps it's something else with your file, better if you submit it as txt file, not as the part of the text within the post, which is actually HTML.

Highlighted
Must be something strange about the file
See attached.
Highlighted

@Chris_Shackleton 

 

Sorry forgot to add data

Highlighted

@Chris_Shackleton 

Both Power Query

image.png

and legacy wizard

image.png

 

import data from this file correctly.

Highlighted

@Sergei Baklan 

 

Thanks for the quick response.

OK so when I load the file I sent (just edited to remove sensitive data) it also works for me.

Seems like the original just needs to be opened in a text editor and re-saved.

 

Solved thanks m8