Convert a csv to xls prior to import

  • Thread starter Thread starter kidkosmo
  • Start date Start date
K

kidkosmo

Hi, Gang,

Hoping someone here can help point me in a direction. I get a series
of reports from Webex that I import into an Access database. The
reports export as csv files which are usually easy enough to import;
however, they include report headers on the file I don't want to
import, so I want to import starting at cell B5. As such, I need to
first convert that file to an Excel file since the transfertext method
can't specify cell ranges. Just changing the file extension to xls
doesn't quite work since it still sees the file as unicode text so I
got into each file, do a save as to excel. There's gotta be a better
way.

I've already got VBA to cycle through the directory to import the
files, but does anyone know of a way to cycle through those csv files
and automatically convert them to xls before importing? I've found
plenty of references to converting xls to csv, but not the other way
around.

Below is an excerpt of the file format I get. It's the first four
lines I want to ignore. Any help is appreciated!!

All sessions in Eastern Daylight Time (New York, GMT-04:00)
Session detail for 'Client Basic Care360 Labs & MedsTraining':
*Attention to Duration ratio: Attentiveness based on total duration of
the session.
**Attention to Attendance ratio: Attentiveness based on how long
participant was in the session.
Participant Name Email
 
Hi, Gang,

Hoping someone here can help point me in a direction. I get a series
of reports from Webex that I import into an Access database. The
reports export as csv files which are usually easy enough to import;
however, they include report headers on the file I don't want to
import, so I want to import starting at cell B5. As such, I need to
first convert that file to an Excel file since the transfertext method
can't specify cell ranges. Just changing the file extension to xls
doesn't quite work since it still sees the file as unicode text so I
got into each file, do a save as to excel. There's gotta be a better
way.

I've already got VBA to cycle through the directory to import the
files, but does anyone know of a way to cycle through those csv files
and automatically convert them to xls before importing? I've found
plenty of references to converting xls to csv, but not the other way
around.

Below is an excerpt of the file format I get. It's the first four
lines I want to ignore. Any help is appreciated!!

All sessions in Eastern Daylight Time (New York, GMT-04:00)
Session detail for 'Client Basic Care360 Labs & MedsTraining':
*Attention to Duration ratio: Attentiveness based on total duration of
the session.
**Attention to Attendance ratio: Attentiveness based on how long
participant was in the session.
Participant Name Email
Click to expand...

Stefan's two step approach is certainly one option; another would be to not
use TransferText at all, but instead use the VBA file-handling code (see the
VBA help for OpenTextFile, TextStream, etc.) to open the file and parse it
yourself.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
kidkosmo said:
Hi, Gang,

Hoping someone here can help point me in a direction. I get a series
of reports from Webex that I import into an Access database. The
reports export as csv files which are usually easy enough to import;
however, they include report headers on the file I don't want to
import, so I want to import starting at cell B5. As such, I need to
first convert that file to an Excel file since the transfertext method
can't specify cell ranges. Just changing the file extension to xls
doesn't quite work since it still sees the file as unicode text so I
got into each file, do a save as to excel. There's gotta be a better
way.

I've already got VBA to cycle through the directory to import the
files, but does anyone know of a way to cycle through those csv files
and automatically convert them to xls before importing? I've found
plenty of references to converting xls to csv, but not the other way
around.

Below is an excerpt of the file format I get. It's the first four
lines I want to ignore. Any help is appreciated!!

All sessions in Eastern Daylight Time (New York, GMT-04:00)
Session detail for 'Client Basic Care360 Labs & MedsTraining':
*Attention to Duration ratio: Attentiveness based on total duration of
the session.
**Attention to Attendance ratio: Attentiveness based on how long
participant was in the session.
Participant Name Email
Click to expand...


A different approach from stefan' or John's suggestions is using the
Windows NT FOR /F command to skip the first lines of the .csv file.

Air code:

Dim strCSVfilename As String ' from your existing code
Dim strCSVnewfilename As String ' can be a temporary file
Dim varLinesToSkip As Variant ' number of lines to skip

Dim strDOScmd As String ' DOS command line string

strCSVfilename = "x"
strCSVnewfilename = "z"
varLinesToSkip = 3

Kill strCSVnewfilename ' delete old output file

strDOScmd = "for /f ""tokens=* skip=" & varLinesToSkip & _
" delims="" %r in (" & strCSVfilename & _
") do echo %r >>" & strCSVnewfilename

Shell strDOScmd

this code will create a copy of the .csv file without the first
varLinesToSkip.
 
Back
Top