Solved CSV import Question (1 Viewer)

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 07:38
Joined
Nov 8, 2005
Messages
3,296
Hi everyone

I have data coming in via a csv file - no problems - want to import into table - I can work this one out no problem -
however the csv file will come in three different layouts

column 1 name standard etc...

version 1 will have x number of fields that are not in 2 or 3 and versions 2 and 3 will be slightly different to each other

will it on an import see that field 8 for example is different? and ignore
(basically will the import look at the field names on the table and assign these values accordingly )

if so then I need to name all of the datafields possible ?(which i don't have an issue with)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:38
Joined
Oct 29, 2018
Messages
21,357
Hi. Are you importing into a staging table? If not, you may be importing and using non normalized data.
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:38
Joined
Jan 23, 2006
Messages
15,364
Where do these csv files originate?
Do the naming conventions identify which format you are receiving?
Do the files represent different things?
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 07:38
Joined
Nov 8, 2005
Messages
3,296
Hi. Are you importing into a staging table? If not, you may be importing and using non normalized data.
Yes so a staging table and then I can "Dlookup and append/add into the right table - I can do this bit no problem
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 07:38
Joined
Nov 8, 2005
Messages
3,296
Where do these csv files originate?
Do the naming conventions identify which format you are receiving?
Do the files represent different things?

csv file one

name
address etc

extra 20 or so fields - say
Apples
Pears
Peaches
etc


Csv file type 2
name add etc
no apples no pears no peaches

but
Kangeroo
Wombat
Polar Bear

CSV file type 3
no apples/pears peaches
might be Kangeroo Wombat Polar bear

but also Football/Tennis /Pingpong



I i have made the apples/pears kangeroo and football up of course but to show that while 75% of the csv files are the same there are differences

so CVS file type 1 has 83/84 fields
cvs file type 2 has 50 fields
CSV files type 3 has 60

I mag get more file types Or sources/products ( file type = product )
 

jdraw

Super Moderator
Staff member
Local time
Today, 10:38
Joined
Jan 23, 2006
Messages
15,364
Your situation, in my view, is a bit like building a house with no clear blueprint. Is it a bungalow, 2 story, split-level? Need some additional info before the physical build.

It sounds like you have Customer or Vendor info.
Then you have a generic cargo "what's in the box".
Fruit? Animals? Sporting Goods?

As dbGuy says you can have a staging table. A simple layout 1 field 100 characters or max length of input record.
You need some additional info to normalize the underlying business entities and their relationships.
Then read the incoming record to the staging table; then check the record to get "cargo type".
General logic to read a specific aspect of the staging record to identify the "cargo type" and put data into the proper table.

Code:
Select Case cargotype
case animals
 Append to animal table
case fruits
 Append to fruit table
case SportingGoods
 Append to SportingGoods table
case else
 'new kind of cargo!!!!
 'need to modify things to deal with new record type'
End select

Good luck with your project.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:38
Joined
May 7, 2009
Messages
19,169
if combinining all columns of these 3 csv files (not duplicating the common columns) will not exceed the total number of columns in a table, which is 255, then it can be done, using VBA.
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 07:38
Joined
Nov 8, 2005
Messages
3,296
Your situation, in my view, is a bit like building a house with no clear blueprint. Is it a bungalow, 2 story, split-level? Need some additional info before the physical build.

It sounds like you have Customer or Vendor info.
Then you have a generic cargo "what's in the box".
Fruit? Animals? Sporting Goods?

As dbGuy says you can have a staging table. A simple layout 1 field 100 characters or max length of input record.
You need some additional info to normalize the underlying business entities and their relationships.
Then read the incoming record to the staging table; then check the record to get "cargo type".
General logic to read a specific aspect of the staging record to identify the "cargo type" and put data into the proper table.

Code:
Select Case cargotype
case animals
Append to animal table
case fruits
Append to fruit table
case SportingGoods
Append to SportingGoods table
case else
'new kind of cargo!!!!
'need to modify things to deal with new record type'
End select

Good luck with your project.
Yeap - got this bit all resolved with a few if statements
If Apples then append to fruit table
if Animals append/add to etc .


its just the import table - and one of the posters has answered this - once i get this up and running ...
I have just asked the data source to put an extra field in place in the data csv so i can do an if source = x then do ... if y then do ... if z do ...
but thansk for your input on somthign that was left a little vague
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 07:38
Joined
Nov 8, 2005
Messages
3,296
if combinining all columns of these 3 csv files (not duplicating the common columns) will not exceed the total number of columns in a table, which is 255, then it can be done, using VBA.
Great - you answered my badly phrased question and save me time doing trial and error

Kind regards

Gary
 

sxschech

Registered User.
Local time
Today, 07:38
Joined
Mar 2, 2010
Messages
791
I have just asked the data source to put an extra field in place in the data csv so i can do an if source = x then do ... if y then do ... if z do ...
Great and easier If they do that. Another alternative if a field is not added--perhaps they can add a unique identifier to the file name of the csv like Export_V1.csv
Export_V2.csv
Export_V3.csv
(V1-Version 1; V2-Version 2; V3-Version 3). Then you can have your code based on those identifier tags in the file name.
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 07:38
Joined
Nov 8, 2005
Messages
3,296
Great and easier If they do that. Another alternative if a field is not added--perhaps they can add a unique identifier to the file name of the csv like Export_V1.csv
Export_V2.csv
Export_V3.csv
(V1-Version 1; V2-Version 2; V3-Version 3). Then you can have your code based on those identifier tags in the file name.
That would be great - however, it's never that easy ... not only is it three versions it's in 4 different currencies and some of the data is different in each I have had a moan at them for this - as I should have been involved a little sooner to resolve the raw data files - but that's life ..
 

Cronk

Registered User.
Local time
Tomorrow, 01:38
Joined
Jul 4, 2013
Messages
2,770
Gary, my suggestion based on a similar situation I encountered some time ago where one government department was supplying CSV info to another for regulatory purposes. It was an automated process where the csv file was an attachment received every 24 hours. Every so often the process failed because the emails ceased. After my intervention, when the process began again, the field order in the csv changed. I suspect the extract query at the sending end was being re-written. (???)

In my case, the csv files had a field header line. I'd open the first line of the csv in VBA to test the field order, check if the order matched an existing order. Each different order was allocated a version number as it was encountered. My process then was then to import the csv into a temporary import table in Access with the fields named F1, F2, .... , Fn, all of which were text fields. Then I'd then run an append query for the appropriate version number.

So say a version 3 csv file was encountered, Query3 would be run to import F1 into Field4, F2 into Field7, etc where Field1, Field2 were the field names in the main import table.

If a new order was encountered, a message alerted the user to call me to add a new query to match the new order. It was impossible to automate this as there were several date fields, numeric, text fields being imported.

I then worried about normalizing the imported data into the working tables.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:38
Joined
Feb 19, 2013
Messages
16,553
providing the column headings are consistent you can import using a sql query - something like this

INSERT INTO myTable (Name, Address)
FROM (SELECT Name, Address FROM [TEXT;DATABASE=C:\path;HDR=Yes].nameoffile.csv) imp

if you need to do conversions you can do that as part of the query - perhaps by joining the imp table to other tables as lookups
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 07:38
Joined
Nov 8, 2005
Messages
3,296
providing the column headings are consistent you can import using a sql query - something like this

INSERT INTO myTable (Name, Address)
FROM (SELECT Name, Address FROM [TEXT;DATABASE=C:\path;HDR=Yes].nameoffile.csv) imp

if you need to do conversions you can do that as part of the query - perhaps by joining the imp table to other tables as lookups

that's great
however I am doing . add method to do what I need - as I need the first part of the import goes in one table , and other parts go into other tables
(I have this bit all resolved -I tested it on part of the import data - but great ) the headings i am trying to standardise - as the source of the data
has as one field in one heading xxxx value £ and in the next csv file xxxx value $ - i am request that is just xxxx value as the file name has the currency on it

many thanks to all I do appreciate your input
once I have this beast sorted I will upload the answer -
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 07:38
Joined
Nov 8, 2005
Messages
3,296
Gary, my suggestion based on a similar situation I encountered some time ago where one government department was supplying CSV info to another for regulatory purposes. It was an automated process where the csv file was an attachment received every 24 hours. Every so often the process failed because the emails ceased. After my intervention, when the process began again, the field order in the csv changed. I suspect the extract query at the sending end was being re-written. (???)

In my case, the csv files had a field header line. I'd open the first line of the csv in VBA to test the field order, check if the order matched an existing order. Each different order was allocated a version number as it was encountered. My process then was then to import the csv into a temporary import table in Access with the fields named F1, F2, .... , Fn, all of which were text fields. Then I'd then run an append query for the appropriate version number.

So say a version 3 csv file was encountered, Query3 would be run to import F1 into Field4, F2 into Field7, etc where Field1, Field2 were the field names in the main import table.

If a new order was encountered, a message alerted the user to call me to add a new query to match the new order. It was impossible to automate this as there were several date fields, numeric, text fields being imported.

I then worried about normalizing the imported data into the working tables.

I had this in the back of my mind - but was trying to stay away from this as if I don't get the data given to me in a usable method it would be a real dogs dinner to sort out - which I can see is how you had to resolve, I may have to take this approach but its a little messy 9 we still have to deal with it )
Your input is appreciated

regards

G
 

gakiss2

Registered User.
Local time
Today, 07:38
Joined
Nov 21, 2018
Messages
168
If there are exactly three different kinds of import tables you could write a process to import each one. Could the user then be asked to select which type? If so then easy peasy. If not then you could have your database 'test' the import table to see which it is. Such as If column 5 = Zebra then use import process 1, If column 6=pears and column 11 = tomato then use import process 2.

These are very generalized thoughts about the approach. Obviously the actual programming would be a different (much more detailed) story.
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 07:38
Joined
Nov 8, 2005
Messages
3,296
Gakiss2
If there are exactly three different kinds of import tables you could write a process to import each one. Could the user then be asked to select which type? If so then easy peasy. If not then you could have your database 'test' the import table to see which it is. Such as If column 5 = Zebra then use import process 1, If column 6=pears and column 11 = tomato then use import process 2.

These are very generalized thoughts about the approach. Obviously the actual programming would be a different (much more detailed) story.
again another train of thought I had also considered and I am using some of this process one of the fields in the fields is earmark for this
if A then add to table A and B ( dlookups to add the right fields to the right d/base tablefield names ) and some fields will need to be added into different tables so far 3 tables possibly 4 and if a new product then 5

I am having words with the datasource to see if we can rename some fields then it should be tickertee boo
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:38
Joined
Sep 12, 2006
Messages
15,613
I do a regular import of something where the structure changes occasionally.

I now have a little table that records the column headers I expect - and I read the first line from the file, and check that the column headers are what I expect before proceeding.
 

GaryPanic

Smoke me a Kipper,Skipper
Local time
Today, 07:38
Joined
Nov 8, 2005
Messages
3,296
Thanks for everyone's input . once I have resolved this i will let people know -
 

Users who are viewing this thread

Top Bottom