Exporting a table to csv (AXP (2002) SP-1)

Topic

New Reply

I have exported a table to a comma-delimited text file, where the values are enclosed in quote marks and separated by commas. Some of my fields in some records are empty, so I get an empty string enclosed in quotes. Here’s what I should be getting:

“data1″,”data 2″,””,”data 4″,””,”data6″

Notice that data3 and data 5 are empty, but there are still placeholders for where the data should be. Just as expected.

One of the fields (and always the same field), however, drops the quotes around the placeholder when the field is empty. When this happens, I get this:

“data1″,”data 2″,””,”data 4″,,”data6″

Notice that data3 and data5 are still empty. Data5 has no quote marks around the empty string, but data3 is formatted correctly. When the field is populated with a value, the value is formatted correctly. I have examined both fields and find nothing out of the ordinary. All fields in the table I am exporting are text fields. All have the exact same properties. I am using the text file to import the data to another application, which requires a precise format. Does this problem sound familiar to anyone?

Reply | Quote

Replies

Seems strange. Would it be possible to let Loungers look at a stripped-down version of the database?

• Make a copy of the database and work with that.
• Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
• In the remaining table(s), remove most records – leave only the minimum number necessary to demonstrate the problem.
• Remove or modify data of a confidential nature.
• Do a compact and repair (Tools/Database Utilities).
• Make a zip file containing the database; it should be below 100KB.
• Attach the zip file to a reply.
  [/list]

Reply | Quote

Hans–

Yes, it does seem strange. The data starts out as an Excel file. I import it into Access then back out again as a text file so that I can get the fields encapsulated by quote marks. (If you know of an easier way to do this, I’m all ears.) There really is no database, but I attached the one that included the table imported from the Excel file, the original Excel file and the text file that was exported.

Here is my process.

1. Import TestDemos.xls to Access using File|Get External Data…|Import…
2. In the Import Wizard, check No Primary Field. Import the header row to make troubleshooting easier. Accept all other defaults.
3. Once the data has been imported, export the newly created table by right clicking the table and click Export…
4. Change Save As Type to Text Files and click Export
5. On the first screen of the Export Wizard, note that the SSN field has quotemarks surrounding the field, whether blank or populated. Notice also that other fields are blank and have quotemarks surrounding the field. Everything looks as it should here.
6. On the second screen, ensure that the delimiter is a comma and that the Text Qualifier is a quotemark. This should be the default. Click Finish.

When you view the resulting text file, note that there are no quote marks around the SSN field. The strange thing is that other field that are blank have the quotemarks as they should. It is only the SSN field. I see nothing out of the ordinary with it either in the original Excel file or in the imported table in Excel.

Reply | Quote

You are correct – for some reason Access is seeing the nulls and not putting quotes around them in the exported result. Of course neither is Excel. One option would be to take the data into Word, make it into a table, and then convert it back to comma delimited – that should give you the empty quotes you are looking for.

Reply | Quote

If you check the contents of the worksheet, you’ll find that the “empty” cells in column I (the SSN column) are really empty, and those in column L (for instance) aren’t, they contain ‘ (an apostrophe). You could replace empty cells in column I by an apostrophe too. It will then be treated icdentically to the other columns by Access. Alternatively, you could create a query in Access with Nz([Field9]) instead of just Field9, and export the query.

Reply | Quote

Thanks to hans and Wendell for helping me figure this out. I manually added in the apostrophe in Excel prior to importing it to Access, and everything worked fine. Now I get to go back and find out what was different in that field in the first place.

Reply | Quote

Do any of the records contain data in data3 and data5? I’ve seen this happen in Excel where you had numeric fields that were blank, but not in Access. You could try creating your own export specification, but I’m not sure that would help.

Reply | Quote