Import CSV file into Excel SS 1

[tonyflora63]

HI

Does anyone know how to import selective coloumns of a .csv file into an excel spread sheet?

Thanks

Tony

 

[Remou]

Here is an example of one possiblity. It takes advantage of the fact that Excel will open CSVs.

'Connection to text directory
Set cn = CreateObject("ADODB.Connection")
strCon="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Docs\;" _
& "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"

cn.Open strCon

'SQL string to copy two named colums (HDR=Yes) from
'one csv file to another.
strSQL="Select ID,Field1 Into New.csv From Old.csv"

cn.Execute strSQL

'Excel
Set xl=CreateObject("Excel.Application")

xl.Workbooks.Open "C:\Docs\New.csv"
xl.Visible=True

 

[tonyflora63]

Remou

Sorry I didn't get back to you sooner. when I run your script I get the following error:

Line 12 char1

"No value given for one or more required parameters

 

[Remou]

What I posted is an example, you will need to set up your own field names. You can use F1, F2 etc as field names if you do not have a header, but you will need to change the connection string. The above will create a schema.ini file, which you may have to delete.

 

[markdmac]

If you know specifically which columns you DON'T want then it may be easier for you to open the CSV as Tony has suggested but then just delete the undesired columns.

Because cells will shift right to left, you should begin your column deletions from the right so you can more easily keep track of the colums.

Dim oXL
Set oXL = CreateObject("Excel.Application")
oXL.Visible = True
[green]
'Open the CSV[/green]
oXL.Workbooks.Open("C:\testcsv.csv")
[green]
' Select the undesired columns working from right to left
' then delete them one at a time[/green]
oXL.Columns("E:E").Select
oXL.Selection.Delete 
oXL.Columns("B:B").Select
oXL.Selection.Delete

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[tonyflora63]

Mark

Two problems.

1. I have to include the full path to the csv file instead of it working in the present working directory.

2. It deletes the first column of data but deletes the rest of the columns all the way to the right which are empty and moves everything over one to the left.

Tony

 

[tonyflora63]

Mark

Sorry

I missed this

"Because cells will shift right to left, you should begin your column deletions from the right so you can more easily keep track of the colums.

 

[markdmac]

Tony,
If you don't include a path it should default to the location the script is executed from.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.

 

[tonyflora63]

Mark

One more thing then this works perfectly for me.

Is there a way to not launch excel and open the file, and just save the changes?

Thanks

Tony

 

[markdmac]

uh, the subject of your thread was to import the data into Excel.

If you don't want it to be visible, then use FALSE.

Otherwise if you prefer to not have to actually use Excel then use the code I provided you in thread329-1433574, which does not use Excel at all.

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.