Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2004
    Posts
    3

    Unanswered: Creating comma-delimited data from .txt file

    I have several thousand rows of data in a text file. As an example, several rows of data might look like this:

    AcmeProduce Order #79132: Produce - 2004/09/26 - 15:42:25 (ET)
    SmithMeats Order #508A: Meat - 2004/09/27 - 14:00:15 (ET)
    AkersDryGoods Order #SEP0904: Canned Goods - 2004/09/28 - 09:25:27 (ET)

    I want to import the data in the text file into a table with columns for vendor, order number, department, date, and time. Unfortunately, the format is as it appears, with no consistent method of delimiting the items within the lines. Items are delimited with spaces, colons and hyphens, but the spaces that appears after the word "Order" and before "(ET)" aren't delimiters. I hope there's a way to prepare this data to import into the table without manually going into the text file and typing in a bunch of commas (Did I mention several thousand rows of data?). The vendors (AcmeProduce, SmithMeats, etc.) and departments (Produce, Meat, etc.) are already entered in separate tables. Is this something I should do with VBA or Excel before importing into Access? Thank you for any help.

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    You said you wanted comma delimitted, but what you described is a need to bring in several thousand rows of data in a text file and to parse it out into fields. Here is a solution. Import the text file into a table as fixed length and remove any separators that the import wizard tries to place, thereby putting the whole text string into field1. I called my table Avid999 and the only field in the table is field 1. Then I created a query that separates the string into the fields that you described. Here is the SQL.

    SELECT Left([Field1],InStr([field1],"Order #")-2) AS Vendor, Mid([field1],InStr([field1],"#")+1,InStr([field1],":")-(InStr([field1],"#")+1)) AS [Order], Mid([field1],InStr([field1],":")+2,InStr([field1],"-")-1-(InStr([field1],":")+2)) AS Department, CDate(Mid([field1],InStr([field1],"-")+2,InStr(InStr([field1],"-")+1,[field1],"-")-1-(InStr([field1],"-")+2))) AS [Date], TimeValue(Mid([field1],InStr(InStr([field1],"-")+1,[field1],"-")+2,InStr([field1],"(")-3-InStr(InStr([field1],"-")+1,[field1],"-"))) AS [Time]
    FROM Avid999;

    TD

  3. #3
    Join Date
    Oct 2004
    Posts
    3
    That worked at treat, TD. Thanks heaps.

  4. #4
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    avid999 you have a solution, fine, but on the other hand i will inform you that if you like to have a delimitted file you could have used the "find and replace" function (ctl-H)
    and replace "order #" by a delimitter and so on.

Posting Permissions

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