Results 1 to 5 of 5
  1. #1
    JeffGrant is offline Novice
    Windows 7 64bit Office 365
    Join Date
    Oct 2021
    Posts
    9

    Import CSV files not working correctly.

    Hi All,



    I have an application that I am moving certain parts of it from using PQ in Excel into Access (ok, I finally saw the light :-))

    At the moment, I am using java to down load a lot of raw data files, those files are merged to created a single csv file for each day. No Problem, been doing this for a long time.
    Each day, the all csv files are imported into Excel using PQ. This is where the system is getting very slow because ALL files are imported everyday as PQ does not have an APPEND Function.
    What I am now wanting to do is move the CSV import to Access, using an APPEND query everyday, then pull the the resultant Access table data back to various Excel Pivot Tables.

    However, I am stick right at the very beginning.

    Importing the merged csv file into Access gives me:
    1. An error message because of field names. So should the field names on line 1 in the csv file be wrapped in quotes ?
    2. Data conversions issues.
    a) When access does it's auto data type detection, how do I set a default of DATE type only not DATE/TIME?
    b) if I set some fields to Currency, the data table ends up with the number in quotes "$2000" ????
    c) some data ends up like this, where the data in the csv file is EXACTLY the same. Although, I must admit this also happens in PQ and Excel when importing. I have learned to live with this



    d) for some reason a field with data like this

    does not end up as a short text field but rather as a long integer.

    Does Access data type detection only look at say the first 100 rows?

    Is there anyway to turn it off?

    I am not new to programming, but the Access import wizard is very frustrating to the un-initiated.

    My application is required to import anywhere between 1000 and 4000 rows per day. The historical files now run into some 300,000 rows and growing. Any direction on how i can solve these issues right at the very begging would be greatly appreciated.

    Thanks in advance

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you upload a sample of your csv with some dummy data so we could see the field names and some of your data.

    There is no Date data type, Date\Time is the only one in Access, you use format to display only the date wherever you need that. And yes, Access looks at the first few rows to determine the data types.

    Usually these kind of problems are solved by the use of a temporary table in which you import your daily csv. Do not worry too much about the data types at this point, just let Access do its thing but make sure nothing is missing. Then you design a new append query to move the imported data into your final "historical" table and it is here where you use conversion functions (CStr, CCur, CDate, etc.) to force each field into its true data type.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Same question as per your other thread here https://www.accessforums.net/showthr...591#post485591

    How are you importing? implication from this thread is you are using the wizard?

    When access does it's auto data type detection, how do I set a default of DATE type only not DATE/TIME?
    that is a formatting issue, not a datatype issue
    if I set some fields to Currency, the data table ends up with the number in quotes "$2000" ????
    , again formatting (I'm assuming the value wasn't $2000.22)
    for some reason a field with data like this
    like what?
    Does Access data type detection only look at say the first 100 rows?
    no, 8 rows - google/bing 'IMEX' as to how the first 8 rows are used to determine datatype. 8 nulls will always be treated as text
    Is there anyway to turn it off?
    You can change this number (8) by modifying your registry - again google/bing how to do this
    Any direction on how i can solve these issues
    perhaps us an upsert query - depends if records with the current import are potentially duplicates from previous uploads. Perhaps normalise your data if not already done so.

    personally I would not use a wizard, but a query, which gives you the opportunity to change datatypes, add additional information etc. A select SQL would look something like

    SELECT *
    FROM (SELECT * FROM [TEXT;DATABASE=C:\Path;HDR=Yes].filename.csv) AS txt
    copy this to a new query sql window, change the path and filename to your requirement and run it, then you can go to query design window and convert it to an append query. Note I usually set HDR=No so the header line comes through which treats everything as text. It is then easy in the append query to set the actual datatype required to be saved.

  4. #4
    JeffGrant is offline Novice
    Windows 7 64bit Office 365
    Join Date
    Oct 2021
    Posts
    9
    Thank you Vlad and Ajax, between the two of you I sorted it out. Very much appreciated

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're very welcome!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 1
    Last Post: 04-10-2019, 11:39 AM
  2. Replies: 2
    Last Post: 11-21-2018, 05:53 AM
  3. WHERE not working correctly?
    By aellistechsupport in forum SQL Server
    Replies: 5
    Last Post: 01-02-2016, 04:24 AM
  4. Won't Import my Numbers Correctly
    By tanyalee123 in forum Import/Export Data
    Replies: 2
    Last Post: 12-18-2014, 01:48 PM
  5. Replies: 10
    Last Post: 06-16-2014, 08:37 AM

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