Annoying feature making a table from CSV file 1

[tedsmith]

I want to create a database table from a CSV file.
Using the following code works well EXCEPT that it interprets data in each column and sets the format of the table to what it thinks is the best (text, number or date).
This is normally OK except then if one of the columns that I really want to be TEXT (because it contains a mix of text and date info ) contains something that looks like a date it forces the whole column to be a date.
This might sound a good idea but any other row fields in this column that DON'T constitute a date are ignored as an error and are not copied to the table.
Is there a way or "turning off" this feature so all table columns are TEXT only?
I can't change the CSV file as it is made by others.
EG.
Name,Dob
Tom,1/4/1936
Dick,25/12/1950
Harry, Deceased (this last line never gets copied)

Sub ConvertCSVToTable()
Dim con As ADODB.Connection
Dim strCn As String
Dim strSQL As String
Set con = New ADODB.Connection
strSQL = "SELECT * INTO Names IN 'C:\Datacollectionserver\Settings.mdb' FROM " & DownLoadFolder & "\" _
    & Format(DateAdd("d", 8 - Weekday(Date, vbMonday), Date), "yyyymmdd") & "_INB.CSV"
strCn = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
    "DBQ=C:\;" & _
    "DefaultDir= C:\;" & _
    "Extended Properties='text;HDR=YES;FMT=Delimited'"
con.Open strCn
con.Execute strSQL
con.Close
Set con = Nothing
End Sub

I could do it by splitting and writing each line but this takes forever whereas the above method only take a second or so for the 70000 row file.

The Unusual CSV filename above is a new file delivered each Monday with a varying filename to suit.

 

[tedsmith]

I agree with you in principle and I am guilty on all charges (some of the time) but I am sometimes faced with situations that are unusual (like having some computers still running on 98 because they are so old they won't run on anything else).
There are plenty of other programmers in our city so perhaps that is why I still get asked to do work at the age of 79 and as you will find it is harder to teach an old dog new tricks the older they get.

With the installers you mention, I understand they can only be worked from the computer in question. On the installation that this question concerns there are around 50 computers mounted in boxes in various parts of the city far out of the reach of humans unless they are perched on a ladder. All changes to them have to be done by remote control (or remote desktop that you can't always rely will be running). They are well firewalled elsewhere from the internet so there is no security issue unless a determined hacker wanted to disable it for some obscure reason.

I'm not suggesting that the security be breached permanently, only temporarily while the files were being updated then returned to normal.

 

[tedsmith]

Re the CODE, just using brackets around [Names] initially fixed the problem.
Thanks to all again for all your trouble.

Using "Names" as a table name still works when I MAKE the table it but it fails when I try to DROP a previously made table of that name and rewrite with new data.
So I changed the name. Using the \ in a SQL statement has never given me trouble so far. CSVPath in the below example has a \ in it anyway.

Here is my final code

Sub ConvertCSVToTableX()
    'With acknowledgement to dilettante
    'CsvPath has trailing "\" character.  Preferably NOT a protected
    'location such as the system drive root!
    Dim CsvFile As String, X As String, a As Integer, CsvPath As String
    Dim Connection As adodb.Connection
    Dim FieldName() As String
    CsvPath = DownLoadFolder & "\" 'from Apps INI file
    CsvFile = Format$(DateAdd("d", 8 - Weekday(Date, vbMonday), Date), "yyyymmdd") _
            & "_INB.CSV"
    
    Open CsvPath & CsvFile For Input As #8
    Line Input #8, X
    FieldName = Split(X, ",")
    Close #8
    Kill CsvPath & "schema.ini"
    On Error GoTo 0
    Close #9
    Open CsvPath & "schema.ini" For Output As #9
    Print #9, "[" & CsvFile & "]"
    Print #9, "Format = CSVDelimited"
    Print #9, "TextDelimiter = """
    Print #9, "MaxScanRows = 0"
    Print #9, "ColNameHeader = True"
    Print #9, "CharacterSet = 1252"
    'add all column names
    For a = 0 To UBound(FieldName)
        Print #9, "Col" & LTrim(a + 1) & " = " & Chr(34) & FieldName(a) & Chr(34) & " Text"
    Next
    Close #9
    
    Set Connection = New adodb.Connection
     With Connection
        .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" _
            & "C:\Datacollectionserver\DCSSettings.mdb'"
        On Error Resume Next 'in case is already there
        .Execute "DROP TABLE TempINB"
         .Execute "SELECT * INTO [TempINB] FROM " _
               & "[Text;Database=" & CsvPath & "].[" & CsvFile & "]", _
                 , _
                 adCmdText Or adExecuteNoRecords
        .Close
    End With
End Sub

Interestingly the execute statement part of it takes 1.7 secs in my original version and 1.8 seconds in the OLEDB version.
It is a 70000 record file with 24 columns.

 

[dilettante]

Come on, are you baiting us?

Hard-coded file numbers?

Missing [tt] On Error Resume Next [/tt] before the [tt] Kill [/tt] statement.

[tt]Chr(34)[/tt] - are you serious? You use a slow version of an unnecessary function call that only means more concatenation must be done.

No [tt] On Error GoTo 0 [/tt] following the [tt].Execute "DROP... [/tt] call.

And how can you not know the column names ahead of time? If you don't, then how could anything ever use the table after importing it?

 

[tedsmith]

<Come on, are you baiting us?> only if you are a fish of legal size but thanks anyway for your concern and the trouble you have gone to to help me.
This is not the actual "Final Final" release that I will distribute.

<Hard-coded file numbers?> yes I like them that way. Having been caught before, I like to keep control of things and show up errors in other subs if I forgot to close a file or if a crash/error trap in another sub bypasses a close statement. I usually precede the open with a close anyway.

<Missing On Error Resume Next before the Kill statement.> I like them that way! I purposely did this during development so it would tell me if it had successfully created the previous try or not. The final release will have the proper arrangement of On Error statements.

<,Chr(34) - are you serious?> Yes, I like then that way too. It made it easier to visualise what was needed. I can't measure any difference in speed, the file printing would take far longer anyway.

<No On Error GoTo 0 following the .Execute "DROP> In the final release I always have an On error Goto 0 just before the Exit Sub statement before the error trap routine. I haven't here shown the error trap routines after an Exit Sub that show a MsgBox in this code. I will naturally have another On Error after the DROP in case something goes wrong in the SELECT statement.

<How can you not know the column names> The whole exercise as I pointed out was because the field names could change but luckily not the order (Original source for the CSV file is from different original databases). I use a datagrid to display the data, dynamically changing it's column names to the new table column names. I use MyRec.Field(#) rather than the name anyway for fixed queries.

I did say I take on some unusual challenges!