Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

    <font color=blue>"data1","data 2","","data 4","","data6"</font color=blue>

    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:

    <font color=blue>"data1","data 2","","data 4",,"data6"</font color=blue>

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Seems strange. Would it be possible to let Loungers look at a stripped-down version of the database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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. <LI>Import TestDemos.xls to Access using <font color=blue>File|Get External Data...|Import...</font color=blue>
      <LI>In the Import Wizard, check <font color=blue>No Primary Field.</font color=blue> Import the header row to make troubleshooting easier. Accept all other defaults.
      <LI>Once the data has been imported, export the newly created table by right clicking the table and click <font color=blue>Export...</font color=blue>
      <LI>Change <font color=blue>Save As Type</font color=blue> to <font color=blue>Text Files</font color=blue> and click <font color=blue>Export</font color=blue>
      <LI>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.
      <LI>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 <font color=blue>Finish.</font color=blue>
    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.
    Attached Files Attached Files

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

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

    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.
    Wendell

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  7. #7
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •