Home

Importing CSV with numbers as text - for bar codes

%3CLINGO-SUB%20id%3D%22lingo-sub-449104%22%20slang%3D%22en-US%22%3EImporting%20CSV%20with%20numbers%20as%20text%20-%20for%20bar%20codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449104%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20occasionally%20export%20both%20contacts%20and%20products%20as%20CSV%20from%20my%20POS%20software%20to%20do%20bulk%20edits%20in%20Excel.%20They%20contain%20both%20phone%20numbers%20(beginning%20with%20%220%22)%20and%20bar%20codes%20(13%20digit).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20to%20be%20able%20to%20import%20these%20using%20a%20wizard%20and%20ensure%20these%20were%20treated%20as%20text%20in%20order%20to%20preserve%20them.%20However%2C%20with%20Office%20365%2C%20this%20wizard%20doesn't%20seem%20to%20give%20me%20that%20option.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E9857840395721%20becomes%3A%3C%2FP%3E%3CP%3E9850000000000%20displayed%20as%26nbsp%3B%3CSPAN%3E9.85E%2B12%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EDoes%20anyone%20know%20how%20to%20import%20CSV%20numbers%20as%20text%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELindsay%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-449104%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-449464%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%20with%20numbers%20as%20text%20-%20for%20bar%20codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-449464%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F318071%22%20target%3D%22_blank%22%3E%40lindswhitehead%3C%2FA%3E%26nbsp%3B%2C%20you%20have%20few%20options.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20open%20Data-%26gt%3BGet%20Data-%26gt%3BQuery%20options%20and%20unselect%20default%20Type%20Detection%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20600px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108572i2433E7CBBF91B7DD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThat%20works%20for%20current%20workbook%20only.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESecond%2C%20if%20above%20option%20is%20ON%2C%20starting%20your%20query%20do%20not%20load%20it%20immediately%2C%20click%20on%20Transform%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20292px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108573i0311005D53EC4720%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eand%20in%20right%20pane%20delete%20automatically%20added%20Changed%20Type%20step%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20241px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108574iBD41EB2FCBD52DF3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eafter%20that%20close%20Power%20Query%20editor%20selecting%20Keep%20changes.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThird%2C%20you%20may%20use%20old%20wizard%20enabling%20it%20in%20options%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20445px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108575i7D0F4D1F3E6ABE37%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eafter%20that%20use%20it%20to%20load%20your%20CSV%20from%20Data-%26gt%3BGet%20Data-%26gt%3BLegacy%20Wizard%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20505px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108576iDC9A8F5448AC3B2F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eselecting%20Text%20for%20the%20column(s)%20on%20third%20step%20of%20the%20wizard%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20259px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F108577i159AD9F732F2B766%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-497309%22%20slang%3D%22en-US%22%3ERe%3A%20Importing%20CSV%20with%20numbers%20as%20text%20-%20for%20bar%20codes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-497309%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%20a%20lot.%20That%20was%20helpful.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
lindswhitehead
New Contributor

Hi,

 

I occasionally export both contacts and products as CSV from my POS software to do bulk edits in Excel. They contain both phone numbers (beginning with "0") and bar codes (13 digit). 

 

I used to be able to import these using a wizard and ensure these were treated as text in order to preserve them. However, with Office 365, this wizard doesn't seem to give me that option. 

 

9857840395721 becomes:

9850000000000 displayed as 9.85E+12


Does anyone know how to import CSV numbers as text?

 

Regards

 

Lindsay

 

2 Replies

@lindswhitehead , you have few options.

 

First, open Data->Get Data->Query options and unselect default Type Detection

image.png

That works for current workbook only.

 

Second, if above option is ON, starting your query do not load it immediately, click on Transform

image.png

and in right pane delete automatically added Changed Type step

image.png

after that close Power Query editor selecting Keep changes.

 

Third, you may use old wizard enabling it in options

image.png

after that use it to load your CSV from Data->Get Data->Legacy Wizard

image.png

selecting Text for the column(s) on third step of the wizard

image.png

@Sergei Baklan 

 

Thanks a lot. That was helpful.

Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies