I seem to be having some issues with saving CSV files and then reopening them. Can someone or many of you help out?

I have file I saved as text. Some of the data has leading 0's that need to be stored as a csv file. I change the format to text the first time I create the file and save it as a csv, it uploads through our system fine. When I open the file to make adjustments it removes all of the leading 0's and changes the format to a number.

Is there something I am missing some place? I thought when I change the format it stays.

Thanks in advance for any help given.

I found one way that works.

Open the CSV file in notepad, and copy and paste it into cell A1. Then turn on record a macro. Use text to columns to import the delimited text into the coulumns. As part of this process, you will be given the opportunity to tell the function which columns you want to import as text. Complete the process and save the macro.

Now whenever you open this spreadsheet, all you have to do is clean out the old data, paste in the new data, and run the macro.

There seems to be something "sticky" about Excel in that this process won't always work. If you try to paste in new data after running the macro, Excel will automatically run text to columns for you and if the number of rows in the new data exceed the number of rows in the old data, then the new rows will import in general format (i.e if it looks like a number, it will be interpreted as a number). To defeat this unwanted help, you will have to close Excel entirely and relaunch it.

Part of the problem is that the CSV file doesn't save formatting, just the data. So, when Excel encounters 000123, it will be converted to the value 123. You need to force Excel to import that column as text somehow.

How are you opening the file to make adjustments?

I tried the following code on two copies of the same file, with two different extensions. The third column was populated with numbers that had leading zeroes. With the file named with a ".txt" extension, it worked. But with the file with the ".csv" extension, the data was imported as numbers, disregarding my definition. It seems the .CSV extension somehow tells Excel to assume certain things about the file that you don't want it to.

At this point, my guess would be that the easiest thing to do would be to save your CSV file with a ".TXT" extension (or save it and rename it). That way you can use code similar to mine to open the file, or when you manuallly open it with File->Open... menu, you will be taken to the wizard where you can manually set the column to Text format.

I'm not overly experienced with this sort of thing, so if someone else posts an idea, by all means try theirs first.

Hope this helps,

Dennis

I agree with Dennis. Where I work a lot of the item numbers begin with a zero, so I’ve done a fair amount of transferring data between different systems using text files.

If you are creating the file yourself you can save it as a .txt (tab delimited) file, unless you must have commas as the delimiter, in which case save as .csv and change the extension afterwards. Also if you receive it from another source as a .csv then change the file extension before opening with Excel.

If you have to open the files often, and always want the same columns formatted as text, then set up some code similar to Dennis’s. That text import wizard gets pretty boring when used a lot.

Out of interest, there does seem to be some kind of link between Excel and .csv files – not sure that it’s always been like this. But I do seem to remember seeing them described as Excel CSV files somewhere, I think.

Carole

Thank you for all of your input. The data is from the result of an sql script that is spit of an application.

I then make my changes in excel and save as a .csv and then it is imported back into the tables with my changes.

I normally never go back into the file after it is saved. My boss and a few others have been trying to go back and change things after I have finished. They came to me with problem.

I had already told them they would need to open with wordpad but didn't like my answer. Becuse it was not nice columns that they could read easy. So I am just double checking to make sure I was correct in what I told them.
Maybe this will keep them out of the files and leave me to the job they hired me for.

I didn't quite understand the bit about "the result of an sql script that is spit of an application". Does this application create a comma delimited file or an Excel workbook? Or are you using an SQL script in Excel to extract data from the application?

Would it be possible/easier to put the data directly into Access and make your changes in the Access environment, rather than using Excel?

If your users make changes to the .CSV file, isn't it too late at that point? Or are they asking you to re-import the data after making changes?

I would either avoid using the .CSV file altogether, or at least delete the .CSV file after you are done importing the data, as it seems to be just a temporary file for importing your data into Access. If users are going to be making changes, it should be to the current data in Access, where you can control what they can do with form controls and VBA code.

Dennis