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