Text File Upload Issue

[shabar]

Hi All

I have a data type issue with text file upload

In my csv file one field comes as "01-Jan-2005 12:00:00 AM" and once upload into access DB I want this filed as Date/Time. But I get a conversion error for this.

I made the date time in regional settings as in the text file format, but still get the same error

I tried to import that filed field as text data type and then converted into date/time at database level after import then it was fine

But since I have this import process regularly I need to revert the data type in DB level each time back to text before the next import


Please let me know if any solution for this


Cheers

Shabar

[alansidman]

Have you set the field type to Date/Time in the import specifications. If you are using the import wizard, click on the advance button to set the data types of each field being imported. This can then be saved for future use.

[shabar]

Thx alansidman for your reply

I have tried what you mentioned. But still no luck...

Cheers

Shabar

[ssanfu]

Would you post a few records from the CSV file that you want to import?

In my testing, each date/time would need to be delimited with double quotes: "01-Jan-2005 12:00:00 AM". Then use the ADVANCED button to set the formats.

[shabar]

Hi ssanfu

My string looks like below

sa ,pop ,PACKAGE,01-Jan-2005 12:00:00 AM,,1,88,000000001C44559BE

It doesn't have double quote as you mentioned.


Cheers

Shabar

[alansidman]

I attempted to bring in the data you presented from a .csv and a .txt file that was comma delimited. Both failed as you had indicated. I then opened the .csv file and did a Text to columns conversion using the wizard that resides on the data tab of the ribbon. I saved the file as a .csv and imported into Access without issue. Date field remained a date/time field.

Alan

[ssanfu]

I can get it to import without errors, but there are several steps.

1) The date MUST be delimited with quotes. Ideally, this would be done when the data is exported to the CSV file.
**Creating an import spec**
2) When importing (the first time), click the ADVANCED button in the dialog box.
3) In the "Text Qualifier" combo box, select the quote used - Single or Double
4) In the Date Order combo box, select "DMY" format
5) Change the date delimiter to "-"
6) Put a check in "Leading Zero in Dates"

You can then click "Save As" to save (create) this Import Spec for later use. Accept the default name or give it a name that you want.
Click OK and continue with the import.

If you save an Import Spec, it can then be use it in VBA code to automate the import.
If the CSV file cannot be exported with the quotes around the date/time, you have two options:

1) write VBA code to read each line, parse each line and and insert the data.
Or
2) Write code to add the quotes to the date/time field in the CSV file, then use the import spec to import the modified file.



Alan,
I use A2k and didn't know about the conversion function. So I guess there are 3 ways to import. good to know. Thanks.

[shabar]

Hi Steve

I performed the same steps that you have mentioned.

But still date field coming as blank after import.

Is there anything to do with machine regional settings and date/time format


Cheers

Shabar

[alansidman]

Shabar, did you attempt my solution in thread #6

[shabar]

Hi alansidman

I just tried your solution. It worked fine for me.

But only issue is how I am gonna automate this. Because this is a frequent process for me


Cheers

Shabar

[alansidman]

Here is a VBA code that you can insert into your .csv file as a macro that will automatically convert your text to columns.

Sub text_to_column()
Application.ScreenUpdating = False
On Error Resume Next
For Each wksht In ActiveWorkbook.Worksheets
    For Each col In wksht.Columns
        Columns(col.Column).TextToColumns _
        Destination:=Cells(1, col.Column), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=True, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    Next col
Next wksht
Application.ScreenUpdating = True
End Sub

[shabar]

Here is a VBA code that you can insert into your .csv file as a macro that will automatically convert your text to columns.

Sub text_to_column()
Application.ScreenUpdating = False
On Error Resume Next
For Each wksht In ActiveWorkbook.Worksheets
    For Each col In wksht.Columns
        Columns(col.Column).TextToColumns _
        Destination:=Cells(1, col.Column), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=False, _
        Semicolon:=False, _
        Comma:=True, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(1, 1), _
        TrailingMinusNumbers:=True
    Next col
Next wksht
Application.ScreenUpdating = True
End Sub

Thax a lot alansidman.

One more thing.... how to set the macro run before import the file to access


Cheers

shabar