View Poll Results: What do you think about the Issue?
- 4. You may not vote on this poll
Not at all a issue/problem!
Strange! Never seen/heard one.
Problem exist; but there is another way to handle it.
Solution implemented seems intelligent!
Thread: Import CSV file
04-06-04, 08:23 #1Registered User
- Join Date
- Apr 2004
- Pune, India
Unanswered: Import CSV file
I am developing a MS Access 97 based application which contains Access forms, queries, tables inside the database itself. The primary function of the application is to -- Read a CSV file, produce a TXT file in another format. For this simplpe exercise, we are importing the CSV file to a table in database using the in built function of MS access 97 doCmd.TransferText. Here, we r facing a strange problem.
The problem is, when we use DOCMD.TRANSFERTEXT inbuilt function to import the CSV file into database, the decimal values were getting truncated. We observed that if a column (in the CSV file) is having values as 8.90, then the value that was imported into the database was 8 in some cases and other cases it was 9.(either Truncate or Round off). In spite of making the data field as Double in the database, the issue was not solved. We were not able to find out the exact cause of this problem, however we observed a trend that if majority of rows contain integer value for this column, it behaved as stated. That is if out of 1000 rows of this column, if 800 have integer values and rest have decimal values, all decimal values were either truncated or rounded off. Sometimes it imported properly, otherwise it failed. Meanwhile, we observed that if we format the column in Excel (CSV format file) to 0.00, then the column was behaving properly. Therefore, we derived that formatting the actual CSV file for some of the required columns should answer the problem. Hence we wrote a procedure to rewrite the complete CSV file in a new file, along with formating some of the columns as stated above. This solved the problem.
As a programmer, I am not convinced by the way I have handled the issue. Can any one of the elite programmers who happened to came across this issue, highlight the exact cause and remedy of the problem.
Help is much needed and appreciated
04-06-04, 10:09 #2Registered User
- Join Date
- Feb 2004
- Dorset UK
Yes I have had problems with the Import text before, even if I set a import template that I had created.
There's two things I can suggest, there maybe better ways.
First bring in the text and split it with known sizes using the textstream method (with Right,left and mid functions)
Dim The_Date as date
Dim fs, a, retstring
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.OpenTextFile("c:\testfile.txt", ForReading, False)
Do While a.AtEndOfStream <> True
The_date = Mid(a.ReadLine,1,10)
Code or query here to append/update recordset.
Or import the text into a 'temp' table with every field set as text. Then using code/query make the text into numeric values as you update.
hope this helps (a little) I know what you are going through with the 'import' function, I don't 100% trust it either !
04-06-04, 14:35 #3Registered User
- Join Date
- Feb 2004
- Chicago, IL
If the file is a CSV file and not an XLS file then you can create a File Specification. Try to link to the file manually and when you get to the Link Text Wizard, press the advanced button. It will show you all the fields that it has found. You can rename the fields and also tell Access what kind of data is in the fields. Once you have set up all the fields and field types, save the file specification and use it in you automated TransferText.
I have never had a problem with plain text files when I use file specifications. However I have had alot of challenging issues with Excel spreadsheets, because you cannot create a file specification for an excel spreadsheet.