Number format with 6 digits from import without leading zeros

[bkeebaugh]

Hello!

I work with two software programs that don't speak to each other. I would like to be able to export to excel from one program (website) and import from excel into the other (accounting program). The problem is that the website only exports client #'s without leading zeros and the accounting system needs the leading zeros to function properly with a 6 digit format. I've used a custom number format 000000 to get the website data to contain the leading zeros. However, the accounting software needs excel imports to be in CSV format and for the cell formats to be text. When I change the client #'s to text format, I lose the leading zeros.

For the past few years I've been manually entering the data, but I would very much like to be able to ex/import to not only save time, but have my work be more accurate. Does anyone have any suggestions?

 

[Micron]

Not sure if the web program drops the zeros or Excel is doing that. Did you try the following if you're pulling data into Excel?

https://absssupport.zendesk.com/hc/en-us/articles/9798128944665-How-to-open-import-text-file-in-excel-without-losing-leading-zeros

An alternative might be to convert the Excel data into text with leading zeros with vba before exporting from/importing into the accounting program. I have to think there's an issue with text format dropping the zeros though. That doesn't make sense to me, but I'm more familiar with how Access behaves with that.

 

[Skyybot]

Under Options - Data uncheck remove leading 0's

 

[Skyybot]

Also, make sure that Notify of data conversion is checked (under additional options)

 

[bkeebaugh]

Not sure if the web program drops the zeros or Excel is doing that. Did you try the following if you're pulling data into Excel?

https://absssupport.zendesk.com/hc/en-us/articles/9798128944665-How-to-open-import-text-file-in-excel-without-losing-leading-zeros

An alternative might be to convert the Excel data into text with leading zeros with vba before exporting from/importing into the accounting program. I have to think there's an issue with text format dropping the zeros though. That doesn't make sense to me, but I'm more familiar with how Access behaves with that.

The web program drops the zeros itself. I'll give this a try, thanks!