Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Exported numbers formatted wrong (2003)

    I exported data (a mixture of text and numbers) from web database as a .csv file. Then I opened the file in Excel and saved it in .xls format. My next step will be to import the file into an Access database. However, the project is on hold because the date in one of the fields got messed up. The field is "Paragraph," and the typical entry is "1-10" (meaning chapter 1, paragraph 10). I'm sure you Excel veterans already know what happened: 1-10 got turned into 10-Jan (as if it were a date). A few cells have something like, "A-21" (appendix A, paragraph 21), and those cells are OK. How do I correct this problem. I've tried Format > Cells, but that action converts a date into a 5-digit number...!? How do I fix this problem?

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

    Re: Exported numbers formatted wrong (2003)

    Do you have a specific reason for using Excel as an intermediate step? Access can import .csv files.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Exported numbers formatted wrong (2003)

    I'd had a problem importing a .csv file, so I put the data thru Excel. But just now

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Exported numbers formatted wrong (2003)

    Well, Hans, I thought it was working, but....

    The field I want to import is named "import.csv". I can open this file in Excel, and it looks fine. The field names display in row 1 just like they're supposed to. I closed that file and tried to import it into Access. Result: All the data gets dumped into one field. Everything

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

    Re: Exported numbers formatted wrong (2003)

    In the first place, this is not a real csv file, it is a tab-delimited file.
    In the second place, the 1st, 2nd, 6th and 7th field contain more than 255 characters in some records. This causes problems when you try to import into a new table - Access doesn't create memo fields when doing that.

    To import, do the following in Access:
    - Create a new table in design view.
    - Create 7 fields, with names exactly as in the text file:

    Field name - Type

    Edit Number - Memo
    Identify the problem - Memo
    Chap - Text (length 20 is enough)
    Para - Text (ditto)
    Type - Text (ditto)
    Comments - Memo
    Change to read - Memo

    - Save the table.
    - Select File | Get External Data | Import...
    - Select Text Files as file type.
    - Open import.csv.
    - Specify Delimited, then click Next.
    - Specify Tab as delimiter.
    - Tick the "First row contains field names" box, then click Next.
    - Select "Into an existing table" and specify the table you just created, then click Next.
    - If you need to repeat this later, click Advances to save an import specification.
    - When done, click Finish.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Exported numbers formatted wrong (2003)

    Hans, I followed your instructions to the letter, but no luck. I think my problem starts back at the application from which I export the files. Just now I deleted the exported files from Windows Explorer and started over. I told the application to export the data as a .csv, and it did so: "export.csv". Then I switched to Access

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

    Re: Exported numbers formatted wrong (2003)

    The file you attached higher up in this thread is a tab-delimited file, so after specifying Delimited in the first step of the Import Text Wizard, you MUST specify Tab as delimiter in step 2. If you omit that, Access won't know how to divide the text into individual fields.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Exported numbers formatted wrong (2003)

    OK, I tried ticking "Delimited" and "Tab Delimited." (By the way, I've tried both Tab and Comma delimiters, with equally poor results.) Result: 140 Type Conversion Failures on the EditNumber field. The resulting table (the one I pre-built) has 163 virtually empty records. The numbers 1 thru 7 appear in the EditNumber field in records 40-46.

    I'm fried. Gotta go home. Thanks for looking at this mess.

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

    Re: Exported numbers formatted wrong (2003)

    The method I described works flawlessly on the sample file you provided. I have attached a database (zipped) with the result. Apparently it's not representative of the "real" file. If you want to pursue this, please post a more representative sample file.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Exported numbers formatted wrong (2003)

    Mm-m-m...not quite flawlessly. The Edit Number field, in many records, displays text instead of the edit number. You got a lot further than I did, Hans, and I applaude you! Now, what's going on with data landing in the wrong fields?

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

    Re: Exported numbers formatted wrong (2003)

    That's because there IS text in the first field/column in the .csv file. Perhaps the original export hasn't gone flawlessly?

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Exported numbers formatted wrong (2003)

    I see that I cannot attach a file with the .csv filename extension. Therefore, I've renamed the file <font face="Georgia">export[1].txt</font face=georgia>. In its original .csv form, this file opened in Excel and appeared exactly as expect it to appear when I import it into Access. The field names were in order, and the field lined up under their respective column headings just like they should. When I imported it, however, the import wizard put the field names in the first row, then appeared to dump everything else into the first column. At any rate, when I opened the renamed file (<font face="Georgia">export[1].txt</font face=georgia> with Excel, the text got all jumbled up again. However, when I changed the name back to its original form, it opened in Excel as before: no problem.

    I don't get it! <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> Does this problem stem from the carriage returns, bullets, and tabs that we've put into those large fields?

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

    Re: Exported numbers formatted wrong (2003)

    Changing an extension from .csv to .txt shouldn't change the file's contents. You can always zip a file if you want to attach it, by the way.

    Carriage returns within the text will mess up the result, because ANY text import mechanism interprets a carriage return as the end of a record. If you view the text file you attached in Word, you'll see that there are paragraphs (=records) that begin with a number, and paragraphs that begin with text. The latter are actually continuations of the previous record, but there is no way you can convince the import mechanism of that, except by avoiding carriage returns within a field.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Exported numbers formatted wrong (2003)

    Hans, thank you very much for helping me understand the nature of this problem. It appears I've been trying to do the impossible.

    Perhaps I can salvage something useful from this discussion. If I may return to the original purpose of this thread, can you help me find a way to keep text that looks like this: 1-20 (meaning chapter 1, paragraph 20) from turning into a date: Jan 20?

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

    Re: Exported numbers formatted wrong (2003)

    Change the extension of the file from .csv to .txt. This gives you much more control over how you import it into Excel - .csv is handled completely automatically, while .txt files cause the Text Import Wizard to be displayed. In step 3, you can specify how each column is to be interpreted. You can specify Text.
    However, the text file you last attached already contains dates instead of text values. Is it a direct export from the original system, or has it been passed through Excel?

Page 1 of 2 12 LastLast

Posting Permissions

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