Results 1 to 8 of 8
  1. #1
    Legend1673 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2016
    Posts
    5

    HELP!...Formatting CSV for Import

    Hello all...



    Need some help. I am working on a project which requires import of a csv file which also creates the table. The file will not have standard headers or column locations. So far everything is working beautifully until I reach an instance where the first X number of rows of data does not contain decimal points but there is data in the column which does (make sense).

    Code Used:
    DoCmd.TransferText TransferType:=acImportDelim, tableName:="llp", _
    FileName:=llpcsv.Value, HasFieldNames:=True

    For Example:
    CSV File = Column1,0,0,0,0,0,0,0...,1.25

    The import is just importing 1 (not 1.25).

    It would work fine to specify that the entire file should be formatted with 2 decimal point when imported, but I can't find how to do this.

    Help please...

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    FileName:=llpcsv.Value,
    You really have a CSV file named as above??? (Maybe that is a control on a form or a variable)

    Manually go through the steps to import the CSV file.
    In the 2nd step, after clicking "First row contains field names", click on the "Advanced" button. Here you can specify the import field types. Change "Integer" or Long Integer" to "Single" or Double".

    You can save this import spec, then use the Import spec in the TransferText command.

    DoCmd.TransferText TransferType:=acImportDelim, tableName:="llp", FileName:=llpcsv.Value, HasFieldNames:=True, SpecificationName = MySpec

    or

    Docmd.TransferText acImportDelim, MySpec, "llp", llpcsv, True



  3. #3
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    ssanfu's solution is the right one, but to explain - the reason you are having a problem is the import routine checks the first few rows for datatype - it doesn't see any dp's so assumes it is an integer. So the other solution is to tell the creator to export to a .csv properly formatted as the data type. The other problem you can get is if the first few rows are blank - the import routine will assume text unless you use ssanfu's solution - often happens with fields like enddates (blank until ended)

  4. #4
    Legend1673 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2016
    Posts
    5
    Thank you for the input, however the data being imported does not always contain the same column headers. This method seems to ignore columns not included in the initial batch....any other suggestions?

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    however the data being imported does not always contain the same column headers
    Consistency is the name of the game for TRANSFERTEXT.
    If the headers and/or the number of columns change, you will always have problems.
    You could open the CSV file in Excel and arrange the columns to a standard layout, then re-save it as a CSV file.
    Or you write a LOT of code to try and handle all of the variations in the CSV files.

  6. #6
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    If a file has additional columns, they will be ignored. If a file is missing some columns, the import will fail.

    in addition to ssanfu's comments you could have different import specs for different files.

  7. #7
    Legend1673 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2016
    Posts
    5
    The situation is the csv is being system created based upon a lot of variables. I would like this access tool to work for multiple departments. So standardization is not an option. I would really like to avoid having people have to reformat the csv file manually.

    The data is all text or numbers (no dates), so formatting the entire file with "0.00" would work just fine.

    And Ssanfu: I don't have a file named llpcsv.value....this is a variable

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Ah....
    I didn't read it as a variable. BTW, the ".Value" is not necessary.


    I would like this access tool to work for multiple departments
    Does each depart. have its own "standardized" CSV format(s)?
    As Ajax said, you can have multiple import specs.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 13
    Last Post: 01-25-2016, 01:36 PM
  2. Replies: 2
    Last Post: 11-12-2015, 10:46 AM
  3. Replies: 3
    Last Post: 01-19-2015, 12:34 PM
  4. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  5. Conditional Formatting vs. VBA Formatting
    By gopherking in forum Reports
    Replies: 5
    Last Post: 11-09-2011, 06:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums