Impoet Text file - Keep leading zeros

[JEB85]

Hi Guys,</SPAN>

I have numerous Excel files that are in the same format which I’ve merged as one text file to import into powerpivot.</SPAN>

One of the columns of data I have on the face look like numbers but I have them stored as text e.g 09312000. This is important because I need to keep the leading zeros.</SPAN>

When I import the data into powerpivot I’m losing the leading zeros.</SPAN>

How do I get around this?</SPAN>

Thanks</SPAN>

 

[JEB85]

Anyone any ideas? I can't find a solution for this anywhere? Powerpivot keeps dropping all the leading zeros on import!

 

[JavierGuillen]

try importing that column as text instead of a numeric type.

if you are using Excel, simply change the data type to text on that column before saving the file.

if its CSV format, be sure to sorround your values with double quotes (as in "0900"), and then PowerPivot will respect the leading zero

 

[JEB85]

brilliant as ever javier. thanks!

 

[JEB85]

Hey Javier, I tried the solutions you suggested today (bit of a delay I know - hectic at work) and I didn't have any luck with either solution.

The data type as text made no difference, I still lost the leading zeros.

I also tried putting the double quotes around the values e.g. "089210" and saved the files as CSV, imported into powerpivot, but then the values in Powerpivot had double quotes around them. A convoluted approach would be to accept this method and then just use the Substitute function to replace the quotation marks with blanks.

There must be a simple solution though (perhaps I've missed something) - there must be thousands of people who import files into powerpivot and need to keep leading zeros.

Incidentally, the reason I'm not importing directly from Excel is that I'm merging multiple Excel files into one big text file to avoid the million rows hurdle.

Have you any idea what I might be doing wrong ie why I'm losing the leading zeros?

Thanks

 

[JavierGuillen]

The issue here is that PowerPivot is incorrecly assuming this is a numeric value (removing the leading zero), and what we are trying to do is to signal it that it is text, in which case there is no need to remove the zero. I have tested the approach of using double quotes in CSVs files with success, but I'm really curious as to why in your case you are getting double quotes. It would appear that PowerPivot already understood it was text, hence the double quotes were unnecessary in your case. I would imagine this could happen is you have some non-numeric value in any row of your column.

Is this the case? can you post a sample of your data?

Also, not sure if you have seen this blog entry but is worth exploring in your case. As far as your multiple Excel files have the same structure, you can 'merge' them in the data load process, and skip the manual merge:

PowerPivot from Identical Structure Excel Files

 

[JEB85]

[TABLE="width: 827"]
<TBODY>[TR]
[TD]Cust No</SPAN>[/TD]
[TD]Amount</SPAN>[/TD]
[TD]Qty</SPAN>[/TD]
[TD]Double Quotes</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]93772900</SPAN>[/TD]
[TD="align: right"]-47.47</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"93772900"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]93772090</SPAN>[/TD]
[TD="align: right"]-58.76</SPAN>[/TD]
[TD="align: right"]2</SPAN>[/TD]
[TD]"93772090"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]93742000</SPAN>[/TD]
[TD="align: right"]-73.39</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"93742000"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]93742000</SPAN>[/TD]
[TD="align: right"]-430.62</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"93742000"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70777028</SPAN>[/TD]
[TD="align: right"]-25.5</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"70777028"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70777028</SPAN>[/TD]
[TD="align: right"]-23.75</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"70777028"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70777028</SPAN>[/TD]
[TD="align: right"]-23.75</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"70777028"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]70547025</SPAN>[/TD]
[TD="align: right"]-23.75</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"70547025"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09312700</SPAN>[/TD]
[TD="align: right"]-31.5</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"09312700"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09312700</SPAN>[/TD]
[TD="align: right"]-6.15</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"09312700"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09312700</SPAN>[/TD]
[TD="align: right"]-34.85</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"09312700"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09312700</SPAN>[/TD]
[TD="align: right"]-34.85</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"09312700"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09312700</SPAN>[/TD]
[TD="align: right"]-26.75</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"09312700"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09312700</SPAN>[/TD]
[TD="align: right"]-26.75</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"09312700"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]85956024</SPAN>[/TD]
[TD="align: right"]-48</SPAN>[/TD]
[TD="align: right"]1</SPAN>[/TD]
[TD]"85956024"</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 4"]This is a small sample set</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7"]The double quotes column is formula based e.g. D2=""""&A2&""""</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]The file is saved as CSV</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 13"]When imported into Powerpivot the Cust No column drops the leading zeros and the Double Quotes column retains the double quotes

Thanks for your help!
</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=9></COLGROUP>[/TABLE]

 

[JavierGuillen]

Try the following

paste this in a notepad file

custno,amount,qty
"09312700",-31.5,1
"09312700",-6.15,1


save it as test.csv

then go to powerpivot, import data from file, and select the csv file just created

you should see the leading zero correctly imported on the cust no. field

with this, open the csv file you had originially saved, and ensure the customer no. is saved using the same format. only one set of double quotes around the numeric customer no. value

 

[JEB85]

Hi Javier,

Copying and pasting the below into notepad, saving as a csv and importing into Powerpivot works great for keeping the leading zeros.

custno,amount,qty
"09312700",-31.5,1
"09312700",-6.15,1

However, I've no idea how I can replicate my data set to the same format? I've tried putting double quotes round the customer no's, single quotes round the customer no's and neither import into powerpivot properly.

All I can think is to keep the double quotes around the numbers, import into Powerpivot, add a calculated column to substitute the double quotes with blanks. Not ideal, when my data set is over 1 million rows. I've searched the internet for ages and had no luck in finding a solution.

Thanks

 

[NickyvV]

Well, I see your problem. You have problem converting the value from PowerPivot to csv as "0993".
Is there a possibility to use an Excel file as import instead of the CSV? You could then add the connection property IMEX=1 (in advanced properties of the connection) to let Excel ignore different datatypes in the same column.

HTH