Compare number of columns in Imported table from CSV with target table if are the same (1 Viewer)

jaryszek

Registered User.
Local time
Today, 09:13
Joined
Aug 25, 2016
Messages
756
Hello,

It is possible to check it easily in SQL? Or should i use VBA?

I imported tables but somebody added newcolumns which i didnt detect and i goe error via queiries.
How to avoid it?

Jacek
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:13
Joined
May 7, 2009
Messages
19,169
what is the SQL of the query:
are you using * in the query.
you can use Exact fieldname to import/export query:

insert into table (field1, field2, field3) select field1, field2, field3 from linkedSheet;
 

Ranman256

Well-known member
Local time
Today, 12:13
Joined
Apr 9, 2015
Messages
4,339
See if the col. Count matches...

Currentdb.tabledefs("table1").fields.count = Currentdb.tabledefs("table2").fields.count
 

Cronk

Registered User.
Local time
Tomorrow, 03:13
Joined
Jul 4, 2013
Messages
2,770
If the import file is not linked, then the suggestion in #3 won't work. Another method is to read the first line of the csv file and compare it to a string of the concatenated field names of the target table. This way provides a check on correspondence in the number of fields, the order of fields as well as the names of the fields.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:13
Joined
Feb 28, 2001
Messages
26,999
It would help to know the exact errors you got in those queries. If you had queries that selected specific columns for the imported table, those queries should have worked unless someone added columns in the middle of the sheet. If you had row headers in the sheet being imported, you would be able to get field names as a possible import option. But we have to guess at what you actually saw.

I will answer the question a different way as well. I had the problem of importing a spreadsheet. It wasn't a CSV case but it DID have the occasional situation of someone deciding to add or remove columns. I required them to use a template sheet that had first-row column names. So what I did was I opened the spreadsheet via an Excel App object and read through the column headers. It was easy to know if something was missing or something had been added. Tedious because we had a lot of columns. But easy because it was always the same thing:

Code:
IF worksheet.Rows(1).Cells.Count <> expected-number then GOTO BadSpreadsheet
IF worksheet.Rows(1).Cells(1) <> "name-1" then GOTO BadSpreadsheet
,,,
IF worksheet.Rows(1).Cells(n) <> "name-n" then GOTO BadSpreadsheet

Didn't matter that I had to repeat the column-name tests over 30 times with 30 different "n" values and 30 different "specific name" values. It was always a trivial test. If you WANTED to, you could add "bells and whistles" by making the IF/THEN actually have a part that tells you which column was wrong. That becomes more tedious - but still not difficult. Using this method, I was able to call out improper use of the template spreadsheet and it was easy to process everything after that test, because I knew I had a decent chance that the spreadsheet was in a decent (if not perfect) format.
 

isladogs

MVP / VIP
Local time
Today, 16:13
Joined
Jan 14, 2017
Messages
18,186
Even if the field count matches (as in post #3), the field names may not match as the user could have added N fields and deleted the same number.

Although you can write lengthy routines as mentioned by Doc or use Cronk's approach, just specifying the field names as @arnelgp suggested is likely to work just as well and with far less effort. That's always been the approach I've used in my own apps when doing an annual archive of 50+ tables of old student data where, over the past year, some fields may have been added to or deleted from several of those tables.
In my case I kept a log of such changes but the principle was the same.

Alternatively you could use this code by Allen Browne to list field names http://allenbrowne.com/func-DAO.html#ShowFields
 

Users who are viewing this thread

Top Bottom