help with extracting part numbers in a text format

[Jeffrey Green]

We have access to a client system. the problem column is the Part Number column. Some parts start with 0, 00, 000. Some also start with a leading 0, but have a non-numeric character in them. Those are fine. They come in as text.

The only way we can get their data into our system is to "extract" and the only option when we "extract" from their portal is into Excel 97-2003.

We we do that, our excel brings in the part numbers that contain only numeric characters as numbers, and then drops the leading zeros. Their IT cannot seem to get us a text file.

How can i get excel on our end to default all columns as text before opening their file?
Is that even possible?

thanks

 

[Joe4]

What kind of a file is that you are opening?
If it is a Text file, it should invoke the Import Wizard, where you can designate the field type of each field (so you can set it to text).

 

[Jeffrey Green]

it automatically, and there is not option but excel 97-2003

 

[Joe4]

Is it a CSV file? What is the extension on the file?

If it truly is an Excel file, you cannot control how Excel already brings it in because it is already in Excel format!
You would create a process for updating/fixing that field after you open it up.

 

[inactiveuserps07]

Jeffrey, it's hard to believe that your client's export options don't allow for creation of a .csv or .txt file. You may have to output first to one of these formats, and then open that file with Excel and Save-As an Excel extension.

 

[Jeffrey Green]

i did manage to open it with anothe program, but if i open in Wordpad, i only get HTML. I even cleaned up the HTML, but only got less than half the rows from the table

 

[Joe4]

If they are truly exporting to Excel, and the leading zeroes are missing, then the dropping off the leading zeroes is happening on their end, and there isn't much you can do about it unless you can figure out how many leading zeroes should be there. Then you can create some formulas or VBA to add them back in.

The reason I was asking about the text files, because I have seen this issue with CSV files. CSV files automatically open in Excel by default, but do text and date conversions like that. As such, you should never use Excel to view a CSV file is you truly want to see what is in there (use a Text Editor). But that does not sound like the case here, from what you are telling us.

i did manage to open it with anothe program, but if i open in Wordpad, i only get HTML. I even cleaned up the HTML, but only got less than half the rows from the table

This makes me question whether or not it truly is an Excel file, or a file in another format that you are opening in Excel. What is the file extension?

 

[Jeffrey Green]

Solved. it is truly excel 97. I called their IT and they are looking at other options. Thanks everyone. I ended up finding the 5000 parts and extracting them into HTML and stripping out the HTML . . . .