INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Annoying feature making a table from CSV file

Annoying feature making a table from CSV file

(OP)
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)

CODE -->

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.

RE: Annoying feature making a table from CSV file

Ted,

It's been a long time ago when I used an .ini (I think) to define a schematic for a text file in order to be able to us in a query. The schematic defines delimited/fixed, every column type. Wish I remembered more, but sipping my French Roast here on the couch on the porch isn't jogging my brain.

RE: Annoying feature making a table from CSV file

Correct me if I am wrong, but are you saying you get the CSV files “made by others” and those files are different (could be different) every time you get them? So a file one time is:

Name  DOB        SomeNumber
Bob   12/12/1937 123
Susie  1/05/2000 678 

And some other time the file is:
DOB         Country   Name
12/12/1988  USA       Bob 

And you make a separate table in your DB for every CSV file you receive?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Annoying feature making a table from CSV file

Andy, he's saying that the structure does NOT change, but the DOB column is mixed with text data.

RE: Annoying feature making a table from CSV file

Specifying a MaxScanRows value larger than the default (set in the registry, typically 25) might also work more often, but the schema.ini is the way to go.

No idea why anyone would use the old Text ODBC Driver though. That was deprecated some time back. Jet 4.0's Text IISAM is available and offers a lot more power with no additional effort. Probably faster too since there is no need to thunk through the ODBC Provider adapter layer.

RE: Annoying feature making a table from CSV file

Ahh, I see the Jet ODBC thunk is being used here. Maybe even scarier.

RE: Annoying feature making a table from CSV file

(OP)
Thanks for your prompt replies. I will try IISAM.

What is "SCARY" about it? It seems to work OK.

Incidentally, I want to create a NEW table each time rather than update an existing table so that if they change the CSV layout, it will still update a table without errors. I use a grid to view the data and modify it accordingly at run time setting the column names to the first line of the CSV file that contains the headers.

RE: Annoying feature making a table from CSV file

>setting the column names to the first line of the CSV file

schema.ini will handle that quite happily

RE: Annoying feature making a table from CSV file

(OP)
Forgive me for being a bit slow but I am a bit confused regarding dilettant's "scary" comment.

Reading the various articles on ISAM and viewing examples, there does not seem to be any clear explanation of the difference between "ODBC" and "ISAM" or why one or the other should be used.

Some examples of what are claimed to be "using ISAM" seem almost the same as the example I posted.

I would much appreciate it if someone please enlightened me or gave me a link to what would explain it in plain English and what I should change to use "IISAM" instead and why?

RE: Annoying feature making a table from CSV file

"Scary" in terms of "looks like somebody copy/pasted some ancient sample code" not "scary" in any other sense. Sort of the way the hair raises when you see DAO used in 2015, or data under the system drive root.

There is a good reason why the old Desktop ODBC Drivers have so many similarities to Jet IISAM SQL syntax, use of schema.ini files, and so on. They are based on code once shared with early versions of Jet.

The risks associated with their use are minor but can smack you hard if you don't pay attention. For example they rely on a different set of defaults in the registry, might invoke Jet 3.5 instead of 4.0 when excreting into an MDB file, and do not get bug fixes and performance improvements. On a machine with Office installed you might have a different set installed than shipped with Windows. Any time you use ODBC with ADO you have to work through an adapter layer, slowing things more. You may clunk into SQL syntax differences such as wildcard pattern symbol and supported data type differences. Databases get opened and closed and opened again query by query. Lots of reasons not to use them.

If doing this works for you, fine. But I wouldn't use them in any production code.

But in general avoid those "here's a million connection strings" sites. Many of them have poor examples dating back to the 1990s.


"Air code" but probably close:

CODE

Sub ConvertCSVToTable(ByVal DataRoot As String)
    'DataRoot has trailing "\" character.  Preferably NOT a protected
    'location such as the system drive root!
    Dim CsvFile As String
    Dim File As Integer
    Dim Connection As ADODB.Connection

    CsvFile = Format$(DateAdd("d", 8 - Weekday(Date, vbMonday), Date), "yyyymmdd") _
            & "_INB.CSV"
    
    On Error Resume Next
    Kill DataRoot & "schema.ini"
    On Error GoTo 0
    File = FreeFile(0)
    Open "schema.ini" For Output As #File
    Print #File, "[" & CsvFile & "]"
    Print #File, "Format = CSVDelimited"
    Print #File, "TextDelimiter = """
    Print #File, "MaxScanRows = 0"
    Print #File, "ColNameHeader = True"
    Print #File, "CharacterSet = 1252"
    Print #File, "Col1 = ""Name"" Text"
    Print #File, "Col2 = ""DOB"" Text"
    Close #File
    
    Set Connection = New ADODB.Connection
    With Connection
        .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" _
            & DataRoot & "Datacollectionserver\Settings.mdb'"
        .Execute "SELECT * INTO Names FROM " _
               & "[Text;Database=" & DataRoot & "].[" & CsvFile & "]", _
                 , _
                 adCmdText Or adExecuteNoRecords
        .Close
    End With
End Sub 

If possible open the MDB for exclusive access and importing will be much quicker than otherwise since no locking is required.

RE: Annoying feature making a table from CSV file

(OP)
Thanks.
The penny drops. It was the "provider" line that had me confused even though it appears to be so obvious (to some people).

One question that remains is can I make a schema.ini file that defines the format of all fields to text without having a line for each field stating its original field name and format?
All examples I have found seem to suggest you have to include the field name.

Otherwise I guess I could pre-read the just first line of the CSV file and make a schema.ini from that.

RE: Annoying feature making a table from CSV file

>All examples I have found seem to suggest you have to include the field name

Did you not read the link I sent? The 'Specifying the Fields' section is pretty clear on this:

Quote:

You can specify field names in a character-delimited text file in two ways:
Include the field names in the first row of the table and set ColNameHeader to True..
• Specify each column by number and designate the column name and data type

In terms of the format, yes you need to specify because otherwise a number of rows are scanned (25 by default as far as I remember, and controlled in schema.ini by the MaxScanRows setting) and a best guess made If you let it guess you may continue to run into the problem you are trying to avoid.

RE: Annoying feature making a table from CSV file

(OP)
Yes I read it. < specify field NAMES in a character-delimited text file in two ways>

The first alternative above appears to mean use the Name only, the second means use Column Number AND Name
I interpret this as you still have to include then column name in both.

I don't want to specify the Field Name, just the format. I want to use the name from the CSV file only.


Regarding not using the root directory, I haven't found a way of sending the new CSV file to a Windows 7 computer that has an app and its data sub folders in Program Files. I usually try to have all files for an app in the one folder and it's sub folders and not scattered throughout the computer. It makes it much easier if it is a requirement to force updates by remote control rather than the usual way of the user doing it from the receiving end.

That why I have sometimes put apps that continuously need data updating from an external source in a folder in the root directory and not in Program Files even though I know it is frowned on for security purposes but luckily it wasn't the type of app that would be worth hacking into and is a case where the opposite of security is required.

Is there a way of programmatically removing the Program Files security temporarily to do the update then closing it back again so I could put everything in Program Files where it belongs?

Hope that doesn't 'scare' people too much?


RE: Annoying feature making a table from CSV file

For data located on the boot drive you do one of two things.

For per-user data you create a subdirectory within the user's profile (i.e. under ssfLOCALAPPDATA). This is normally a per-user first-run action since an installer has no idea how many users there will be or what their profile names are.

For per-machine data you create a subdirectory within the mahine profile (under ssfCOMMONAPPDATA). This is normally an installer action.

In both cases the created subdirectory gets "owner" security inherited from its parent directory. Commonly for per-machine data such subdirectories your installer should also alter the security, and since it is running elevated this isn't much of a challenge. Typically some locations need to have full read/write/create/delete access for all authenticated users.

In both cases to reduce collisions with other applications you create a "company name" subdirectory and under that an "application name" subdirectory.

Nothing new here, these have been the rules of the road since at least Windows 95 Desktop Update when Shell Special Folders were introduced and supported by shell32.dll 4.71 in 1997.


For data drives all bets are off and you can do pretty mcuh as you choose.

RE: Annoying feature making a table from CSV file

(OP)
Thanks.
Yes I was aware you could do that but -
Unfortunately the users/installers that are likely to use the sort of programs I supply wouldn't have a hope in hell of understanding or doing manually what you say. The problem usually turns up in a few years time when the original machine lease runs out and the program has to be reinstalled in a new machine (sometimes with a new OS)

I would need an install program that did all this automatically (which I haven't at the moment)

Also any user has to be able to use the data.

I hate getting a "help" phone call from halfway across the world when I am on holidays!

Putting everything it in one folder in the root directory and sharing this is the easy way even if it is bad. The data involved is of no use to hackers anyway. It would be nice to be able to bypass the Program Files security (like PDW does) to reinstall by remote.

RE: Annoying feature making a table from CSV file

>I interpret this as you still have to include then column name in both

Upon reflection I think you are right. It has been a long time since I used schema.ini. But you can pull those names from the CSV (or the Names table) to build the schema.ini

RE: Annoying feature making a table from CSV file

(OP)
Regarding dilettante's kind suggestion I get an error on the execute line

Run time error '-2147467259 (8000-4005)':
Methiod 'Execute' of object'_connection' failed

This seems to be the notification for a number of error types,(including many that can be fixed by sending money to someone!)

I'm sure I have the path/filenames correct but I can seem to find any example on the web that looks anything like this.
are there extras particular references I need to add (it is not giving the usual error advice like a missing reference does).

I have made a scheme condtruct routine from the original table that uses all fieldnames from the header (of which there are 24)

RE: Annoying feature making a table from CSV file

There were two problems, one major and the other cosmetic.

The major problem is that "Names" seems to be a reserved word in Jet SQL, even though I don't find it documented anywhere. The cosmetic issue is using a trailing "\" in the Database value in the SQL statement.

So add brackets around the table name, always a safe practice anyway. Here is a standalone tested example that does that but keeps the "\" just to show it was not the culprit:

CODE

Option Explicit

Private Sub SetTheStage(ByVal DataRoot As String)
    'Create fresh CSV file to import, create fresh MDB to import into.
    Dim CsvFile As String
    Dim File As Integer
    Dim Connection As ADODB.Connection

    CsvFile = Format$(DateAdd("d", 8 - Weekday(Date, vbMonday), Date), "yyyymmdd") _
            & "_INB.CSV"
    On Error Resume Next
    Kill DataRoot & CsvFile
    On Error GoTo 0
    File = FreeFile(0)
    Open DataRoot & CsvFile For Output As #File
    Print #File, "Name, DOB"
    Print #File, "Fred Flintstone, 1940-01-05"
    Print #File, "Barney Rubble, 1940-04-30"
    Close #File
    
    On Error Resume Next
    GetAttr DataRoot & "Datacollectionserver"
    If Err.Number = 0 Then
        GetAttr DataRoot & "Datacollectionserver\Settings.mdb"
        If Err.Number = 0 Then
            Kill DataRoot & "Datacollectionserver\Settings.mdb"
        End If
    Else
        Err.Clear
        MkDir DataRoot & "Datacollectionserver"
    End If
    On Error GoTo 0
    
    'Best to late-bind ADOX due to binary compatibility breaks Microsoft made.
    With CreateObject("ADOX.Catalog")
        .Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" _
              & DataRoot & "Datacollectionserver\Settings.mdb'"
    End With
End Sub

Private Sub ConvertCSVToTable(ByVal DataRoot As String)
    'DataRoot has trailing "\" character.  Preferably NOT a protected
    'location such as the system drive root!
    Dim CsvFile As String
    Dim File As Integer
    Dim Connection As ADODB.Connection

    CsvFile = Format$(DateAdd("d", 8 - Weekday(Date, vbMonday), Date), "yyyymmdd") _
            & "_INB.CSV"
    
    On Error Resume Next
    Kill DataRoot & "schema.ini"
    On Error GoTo 0
    File = FreeFile(0)
    Open DataRoot & "schema.ini" For Output As #File
    Print #File, "[" & CsvFile & "]"
    Print #File, "Format = CSVDelimited"
    Print #File, "TextDelimiter = """
    Print #File, "MaxScanRows = 0"
    Print #File, "ColNameHeader = True"
    Print #File, "CharacterSet = 1252"
    Print #File, "Col1 = ""Name"" Text"
    Print #File, "Col2 = ""DOB"" Text"
    Close #File
    
    Set Connection = New ADODB.Connection
    With Connection
        .Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" _
            & DataRoot & "Datacollectionserver\Settings.mdb'"
        .Execute "SELECT * INTO [Names] FROM " _
               & "[Text;Database=" & DataRoot & "].[" & CsvFile & "]", _
                 , _
                 adCmdText Or adExecuteNoRecords
        .Close
    End With
End Sub

Private Sub Main()
    SetTheStage App.Path & "\"
    ConvertCSVToTable App.Path & "\"
End Sub 

RE: Annoying feature making a table from CSV file

It seems like you always have a reason to do things incorrectly.

You should not be trying to deploy any VB6 programs without creating and testing a proper installer first, preferably one using Windows Installer for safety and to be able to do things like set folder security during installation.


If you really want a "user visible" dumping ground for data there are obvious candidates, far safer than opening up security on the system drive root, which just opens the door to malware.

One of the more obvious candidates is the Public folder, but I assume you'd quickly counter with some BS like "sorry, my users are still running the unsupported and unsafe Windows XP..."

Then you have alternatives that work even on crusty old OSs such as ssfCOMMONDESKTOPDIR and ssfCOMMONDOCUMENTS (which is not predefined in any typelib but equal to CSIDL_COMMON_DOCUMENTS = &H2E&). Of course once again these default to "owner" access so your installer should create a subdirectory there and set the desired access on it.

RE: Annoying feature making a table from CSV file

(OP)
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.

RE: Annoying feature making a table from CSV file

(OP)
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

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.

RE: Annoying feature making a table from CSV file

Come on, are you baiting us? wink

Hard-coded file numbers?

Missing On Error Resume Next before the Kill statement.

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

No On Error GoTo 0 following the .Execute "DROP... 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?

RE: Annoying feature making a table from CSV file

(OP)
<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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close