How to import one CSV in two tables (1 Viewer)

emare

New member
Local time
Today, 13:51
Joined
Nov 15, 2016
Messages
4
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

Nifty Access Guy
Staff member
Local time
Today, 12:51
Joined
Jul 9, 2003
Messages
16,245
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/
 
Last edited:

emare

New member
Local time
Today, 13:51
Joined
Nov 15, 2016
Messages
4
Thanks Uncle
 

plog

Banishment Pending
Local time
Today, 07:51
Joined
May 11, 2011
Messages
11,613
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_

Longboard on the internet
Local time
Today, 05:51
Joined
Sep 12, 2017
Messages
2,111
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

Super Moderator
Staff member
Local time
Today, 08:51
Joined
Feb 19, 2002
Messages
42,981
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.
 

Users who are viewing this thread

Top Bottom