Import data from file

[glenquagmire31]

Hello all.

I have been trying to import data from a csv file unsuccessfully. Upon examining the file it is full of SQL insert statements.
Does anyone have any idea how I could go about importing the data to a Table?
I am including the first 5 rows of the file below.

REM INSERTING into EXPORT_TABLE
SET DEFINE OFF
Insert into EXPORT_TABLE (TREATMENT,YEAR,MONTH,ATC_CODE,ACT_SUBS,DOSE_UNIT,CONTENT,PIECES_PER_PACKAGE,FORM_CODE,SPECIALTY,ZIP_CODE,AGE,SEX,ICD10_CODE) values ('1858021806','2022','05','J01CA04','AMOX TRIHYDRATE','DSX','1G/TAB','18','DISP.TAB','GP','18532','46','F','K05.2')
Insert into EXPORT_TABLE (TREATMENT,YEAR,MONTH,ATC_CODE,ACT_SUBS,DOSE_UNIT,CONTENT,PIECES_PER_PACKAGE,FORM_CODE,SPECIALTY,ZIP_CODE,AGE,SEX,ICD10_CODE) values ('1856614429','2022','05','J01MA12','LEVOFLOXACIN HEMIHYDRATE','DSX','500MG/TAB','5','F.C.TAB','PNEU','74100','49','M','J41.1')

and it goes on and on....

 

[theDBguy]

Hi. Welcome to AWF!

Are you using SQL Server or Access?

 

[Ranman256]

copy the file every time to the same file name, ie: c:\temp\File2Import.csv
link the csv file as external table. (using Import Spec. save import spec)
create a query to import the data.
put query in a macro.

then the procedure is:
1. overwrite the new file to: c:\temp\File2Import.csv
2. run import macro.
done.

 

[glenquagmire31]

Hi. Welcome to AWF!

Are you using SQL Server or Access?

hello and thanks for the reply!
I am using Ms Access

 

[The_Doc_Man]

The problem is that you don't have a CSV file there. It will look at (for example)

(TREATMENT,YEAR,MONTH,ATC_CODE,ACT_SUBS,DOSE_UNIT,CONTENT,PIECES_PER_PACKAGE,FORM_CODE,SPECIALTY,ZIP_CODE,AGE,SEX,ICD10_CODE) values ('1858021806','2022','05','J01CA04','AMOX TRIHYDRATE','DSX','1G/TAB','18','DISP.TAB','GP','18532','46','F','K05.2')

It will see the comma-separated field and value lists as two sets of 15 separate fields - but they are not. At least, I doubt you intended them to be seen as individual fields.

What are you going to do with these things? They look like executable SQL code, which is certainly possible. But unless there is some punctuation that you omitted, that ain't a CSV file and won't behave like one.

 

[GaP42]

Welcome to the forum.
Your csv file contains SQL statements that are to insert / append data to a table (EXPORT TABLE). Are you wanting to import the statements or simply the data that is associated with those statements?

Was this file generated for you as an export from another db system - SQL Server? Can you ask for a change so that just the data is exported?

If the data is all that you need, your first step will be to get your csv file in order, so that the SQL statement is not exported itself, just the data (with the heading on the first row). ie
TREATMENT,YEAR,MONTH,ATC_CODE,ACT_SUBS,DOSE_UNIT,CONTENT,PIECES_PER_PACKAGE,FORM_CODE,SPECIALTY,ZIP_CODE,AGE,SEX,ICD10_CODE

The data is structured consistently throughout the csv I hope - medications issued for treatment of ICD10 defined conditions with some demographics, de-identified.

Once in a that form, you will probably then find it easiest to import into Access through the guided import process Access provides, from which you can save an import specification and operate repeatedly as ranman suggests.

 

[Josef P.]

I would omit the conversion to CSV and run the SQL insert statement already in the text.

Possible procedure for Access with the shown data:

1. Read text into string variable (file open & Co)
2. Split string .. StringArray= split(xxxx, "Insert into")
3. Run array from 1 (not from 0), put "Insert into" back in front and execute the SQL statements one by one.

 

[theDBguy]

hello and thanks for the reply!
I am using Ms Access

Hi. Thanks for the clarification. As others have already said, you actually have a SQL file rather than a CSV file. Each INSERT statement in the file is meant to add records to an existing table. You could try copying and pasting each INSERT statement in an Access query, but you'll have to make sure you have those tables already created in your database. Do you? Otherwise, you could try what others said and try to get an actual CSV file from the source.

 

[glenquagmire31]

thank you all for the replies!!!
Kinda figured I would have to clean up that mess of a file before even attempting to import it...

Will give it a go!