Reading specific rows and columns fron .csv files using ACCESS VBA

[newpgm]

Hi All, I have a csv file and I have to create a table by reading specific rows and columns from the csv file.
I would like to do this using VBA..
1) I have to read 1st rows 1 st column after colon as my filename field ( Document Name: 12-18-2009 Panel xls )
2) Then I have to read 8th row, 2nd colum Dec18 2009 as run_date(Run Date: Friday December 18 2009 10:59:03)
3)Finally I want to read columns2,3,4, from row 29 to row 150 as field1, field2 field 3 ( sample, test, value).After reading I have to transpose these, i.e make columns to rows and vice versa. so there will be at the maxium 8 rows and 9 columns . Then I have to append the filename and run_date to create a table. I have done this in SAS, now planning to do it in ACCESS

can any one help me start this coding in ACCESS. I created a form to accept the csv file name. In my code this file is read and create a table out of all these values to process.

Thanks

 

[PHV]

In my code this file is read and create a table
Which code ?
Where are you stuck with this code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newpgm]

sorry for the confusion. My code is in SAS not Access. I have to start brand new module.

*let csv_file=Mountain plate1 12-12-2009.csv;


%macro file_name (dsn=);

options mprint mlogic symbolgen NOXWAIT NOXSYNC ;

x "start excel";

options missing =0;

data _null_ ;
x = SLEEP(3) ;
run ;
filename cmds DDE 'excel|system' ;

filename fname DDE "Excel|G:\csv_files\&dsn.!R1C1:R1C6" notab ;
filename op DDE "Excel|G:\csv_files\&dsn.!R3C1:R3C2" notab ;
filename dt DDE "Excel|G:\csv_files\&dsn.!R8C2:R8C3" notab ;
filename ct_val DDE "Excel|G:\csv_files\&dsn.!R29C2:R150C4" notab ;

data _null_ ;
file cmds ;
put %unquote(%str(%'[FILE-OPEN("G:\csv_files\&dsn.")]%')) ;

run ;

/* get and store file name for all the records*/

data fname( drop= doc_name);
infile fname dsd dlm='09'x missover pad lrecl=300;
length doc_name $ 50;
input doc_name $;
run_file_name = substr(doc_name,16);
label run_file_name ="RUN_FILE_NAME";
run;
/* get run date for this file*/


data dt1( keep=Date_Tested Datetime_Tested Time_Tested);
infile dt dsd dlm='09'x missover pad lrecl=300;
length dt $ 15 tm $13;
input dt $ tm $;
format Date_Tested date9. datetime.;
temp_dt = scan(dt, -1)||substr(dt,1,3)|| substr( tm,1,4) ;
Date_Tested=input(temp_dt,date9.);

/* get all the records for this files*/

data ct_val ;
infile ct_val dsd dlm='09'x missover pad lrecl=300 ;
length SampleID $ 10 PRIMER_PROBE_SET $ 10;
format ct_num 8.2;

input SampleID $
PRIMER_SET $
CT $ ;
ct_num=input (ct,8.);
SampleID =upcase(SampleID);
label SampleID = "SampleID"
PRIMER_SET ="Primer_SET"
CT ="CT_Value";
if sampleID = ' ' then delete;

run ;

*** Close Excel ***;
data _null_ ;
file cmds ;
put "[FILE-CLOSE()]" ;
put "[QUIT()]" ;
run ;

/* proc transpose suffix= option doesn't work in 9.1 so a work around*/
data ct_val_suf;
set ct_val;
PRIMER_PROBE_SET=trim(left(trim(left(PRIMER_SET))||"_"||"Ct"));
run;

proc sort data=ct_val_suf out=ct_val_sort;
by SampleID;
run;

proc transpose data=ct_val_sort(keep= SampleID primer_set ct_num)
out=ct_tp( drop =_name_);
by SampleID;
id Primer_SET;
var ct_num;
run;

data abi ;
format State $50. id_number best12. datetime_tested datetime19.;
if _n_=1 then set dt1;
set ct_tp;
state=' ';
id_number=input(sampleID,best12.);
run;

/*check exiting table and assign a test no*/
proc sort data= ABI out=abi_st;
by id_number datetime_tested;
run;

data rrt ;
merge my_rrt ( keep =id_number datetime_tested in=rrt)
abi_st (in=abi);
by id_number datetime_tested;
format TestNo $6. ;
/* process only records that are in the excel sheet*/
retain i ;
if first.id_number then i = 0;
i + 1;
if date_tested > . then do;
if i=1 then TestNo ='Test 1';
else if i=2 then TestNo='Test 2';
else if i=3 then TestNo='Test 3';
else if i=4 then TestNo='Test 4';
if abi;
output;
end;
drop i;
run;

data RRT_XL;
set rrt( drop= id_number datetime_tested time_tested date_tested);
format Results $20.;
Results=' ';
run;

proc sort data=RRT_XL;
by sampleID;
run;

data Assay_details;
merge oper fname ;
run;

%mend;
*file_name( dsn=12-12-2009 Full Panel Plate 2.csv);
%file_name( dsn=&csv_file.);
*********END*************;

 

[PHV]

So, what have you tried so far ?
You may consider the DoCmd.TransferSpreadsheet method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newpgm]

I want to do some thing like below.

Sub TEST_ImportCSV()

strFilePath = "G:\"
strFileName = "12-18-2009 Panel " & ".CSV"
strQuery="qryImport"

DoCmd.TransferText acExportDelim, , strQuery, strFilePath & strFileName, False

end sub
but how do I replace my file name with the parameter entered in the form and process that. I am completely new to VBA coding. I can write simple SQL queries and simple code with button click but can't write complex code in VBA.
I can import csv using macro but I want to do this using VBA only.

 

[PHV]

how do I replace my file name with the parameter entered in the form
strFileName = Forms![name of form]![name of control] & ".CSV"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[newpgm]

My .csv is imported into a table. Now using VBA code how can I read R29C2:R150C4

 

[PHV]

Use a Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886