How to import one CSV in two tables

emare · Oct 20, 2017

Hello,

I'm struggling to find an easy way to import below CSV into two tables.
No need to say that one table has to contain only the 'H' records and the second tables only the 'R' records.

Thanks a lot for helping.

Code:

H;313413006;15I10;C35;TI39488;15.8;16.00;0.756;0.684;13.624;12.576;14.380;13.260;
R;313413006;15I10;S7014 ACEGA/HCP4AVG275;12 29;2;1.048;25836518;200358;
R;313413006;15I10;S7014 ACEGA/HCP4AVG275;12 29;23;12.052;25836519;200358;
H;313413008;15I10;C21;TI39488;-43.7;0.10;0.084;0.076;0.102;0.094;0.186;0.170;
R;313413008;15I10;71808 ACDGB/P4;12 29;2;0.098;25836522;92439;
H;313413225;15I10;P02;CX8152;-4.8;141.80;25.640;21.842;130.141;120.131;155.782;141.972;
R;313413225;15I10;YSP 207-106 SB-2F;UR 34;264;125.136;25836816;208565;
H;313411926;15I10;C21;TI39488;-13.0;1.50;0.084;0.076;1.710;1.578;1.794;1.654;
R;313411926;15I10;7004 CEGA/HCVQ126;12 29;1;0.063;25834143;177408;
R;313411926;15I10;S71909 CEGA/HCP4A;12 29;1;0.144;25834144;176108;
R;313411926;15I10;7013 ACEGA/HCH1VQ126;12 29;2;0.892;25834145;176895;
R;313411926;15I10;7001 CEGA/HCVQ126;12 29;1;0.023;25834146;177434;
R;313411926;15I10;7003 CEGA/HCVQ126;12 29;2;0.077;25834147;179820;
R;313411926;15I10;71909 ACEGA/HCVQ126;12 29;2;0.382;25834148;179997;
R;313411926;15I10;7004 CEGA/HCVQ126;12 29;1;0.063;25838032;177408;
H;313411930;15I10;C21;TI39488;-19.0;0.20;0.084;0.076;0.173;0.160;0.257;0.236;
R;313411930;15I10;6003-2RSH;12 29;4;0.167;25834152;88842;
H;313411938;15I10;C21;TI39488;-11.5;0.20;0.084;0.076;0.152;0.140;0.236;0.216;
R;313411938;15I10;6004-2RSH;12 29;2;0.146;25834162;90386;
....
...
..
.

Uncle Gizmo · Oct 20, 2017

Import the comma separated value file into a staging table. Then run two queries against the staging table. One query to extract "H" into a new table, the other to extract "R" into a new table.

YouTube Video HERE:-

http://www.niftyaccess.com/import-csv/

emare · Oct 20, 2017

Thanks Uncle

plog · Oct 20, 2017

No need to say that one table has to contain only the 'H' records and the second tables only the 'R' records.

2 tables with the exact same structure is a poor structure. Why must the data be seperated by table? What are you doing with these tables? The correct way to handle this would just to use a SELECT query to work with just the data you need.

Mark_ · Oct 20, 2017

plog said:
2 tables with the exact same structure is a poor structure. Why must the data be seperated by table? What are you doing with these tables? The correct way to handle this would just to use a SELECT query to work with just the data you need.

R and H records have different numbers of fields. Not the same structure.

Pat Hartman · Oct 20, 2017

I wouldn't import the file. I would link to it and then run append queries. Make sure you control the data types. You might not want to use the ones the import wizard assigns.

If you need to repeat the import, save the spec you created (press the Advanced button) and give it a short, meaningful name. Then you can reference the spec using TransferText and automate the import process.

How to import one CSV in two tables

emare

New member

Uncle Gizmo

Nifty Access Guy

emare

New member

plog

Banishment Pending

Mark_

Longboard on the internet

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread