CSV to Access? *Tab-delimited file*

ASP Free Forums Sponsor:

February 13th, 2006, 03:25 AM

DOLL

Contributing User

Join Date: Feb 2006

Posts: 32

Time spent in forums: 7 h 50 m 53 sec
Reputation Power: 3

CSV to Access? *Tab-delimited file*

Hola.

I have a question on Importing tab-delimited CSV to Access.

Currently I have a CSV file. Eg. *CSV1.txt*
_________________________
Field1 Field2 Field3 Field4
XX | XX | XX | XX
CC | CC | CC | CC
EE | EE | EE | EE
________________________

As you can see, the file is a tab-delimited file. So far and till now, I have only found codes that only import data that are separated by commas.

I've tried to tweak the codes but still up to no avail. I'm only able to only import 1 column from the CSV file.

Any leads to this?? Do help me muchachas en muchachos. It's reallllly important.

February 13th, 2006, 03:37 AM

nofriends

Senior Water Wizard

ASP Free God 11th Plane (10000 - 10499 posts)

Join Date: Aug 2004

Location: Cape Town, RSA

Posts: 10,186

Folding Points: 107973 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 3 Months 2 Weeks 2 Days 7 h 36 m 24 sec
Reputation Power: 699

could you post the code you have now?

you should be able to read in the whole line and split it on vbTAB
to get the columns

__________________
Look! Its a ShemZilla

February 13th, 2006, 03:45 AM

DOLL

Contributing User

Join Date: Feb 2006

Posts: 32

Time spent in forums: 7 h 50 m 53 sec
Reputation Power: 3

By all means muchacho..Hopefully you can help me solve this.

Code:

<%@ Language="VBScript" %>

<%
  'CONNECT TO THE DATABASE
  set objconn = Server.CreateObject("ADODB.Connection")
  objconn.Provider="Microsoft.Jet.OLEDB.4.0"
  objconn.Open Server.MapPath("csv.mdb")


  csv_to_read="test_csv.csv"
  set fso = createobject("scripting.filesystemobject")
  set act = fso.opentextfile(server.mappath(csv_to_read),1,Fal  se)

  dim sline
  dim sSeg
  
  Do Until act.AtEndOfStream
  
  sline=act.readline
  sSeg=split(sline,",") <-- Here. I don't know what to replace.

  dim strsql
  strsql="INSERT INTO CSV (CompanyID, CompanyName, Address, Country)"
  strsql=strsql & "VALUES('"&sSeg(0)&"', '"&sSeg(1)&"', 
  '"&sSeg(2)"', '"&sSeg(3)&"')"

  objconn.execute strsql
     
  loop
  act.close
  set act=nothing
   
  'CLOSE THE CONNECTION AND CLEAN UP
  objconn.close
  set objconn=nothing
%>

You see, I have 4 field names that I need to import but the delimitor that is used in this code is comma. And I don't know how to change it or replace it since I've tried many ways to tweak the codes. It just won't import the rest. It only imports 1 column from the CSV file.

February 13th, 2006, 04:03 AM

nofriends

Senior Water Wizard

Join Date: Aug 2004

Location: Cape Town, RSA

Posts: 10,186

Time spent in forums: 3 Months 2 Weeks 2 Days 7 h 36 m 24 sec
Reputation Power: 699

try the following

Code:

sSeg=split(sline, vbTAB)

hope this helps

February 13th, 2006, 05:08 AM

DOLL

Contributing User

Join Date: Feb 2006

Posts: 32

Time spent in forums: 7 h 50 m 53 sec
Reputation Power: 3

oohh..i never think of that..i'll try that tomorrow..i'm off! toodles!

February 13th, 2006, 05:18 AM

nofriends

Senior Water Wizard

Join Date: Aug 2004

Location: Cape Town, RSA

Posts: 10,186

Time spent in forums: 3 Months 2 Weeks 2 Days 7 h 36 m 24 sec
Reputation Power: 699

I have tested it already and it works

let us know what happens

February 13th, 2006, 07:55 PM

DOLL

Contributing User

Join Date: Feb 2006

Posts: 32

Time spent in forums: 7 h 50 m 53 sec
Reputation Power: 3

It works man! The first thing that I did the moment I reach my work place is to replace the (sLine, "") to (sLine, vbTab) and boy does it work like magic!

Gracias muchacho! Really helped me in there.

But then what happens if the CSV file have more than 20 columns of data?
==============================================
Field 1..|..Field 2..|..Field 3..|..............................|..Field 22
==============================================
QQ...... |..WW.....|....................................... .....| WW |

What would happen there? Actually how does it reads a CSV file? What if there are titles on the header of the CSV file? Eg.
Invalid Purchases 20/05/2005
... ... .. .. ... .... .... .... .... .... .... ....
... ... ...... ... . . . . .. . ..... .....

How about that?

February 14th, 2006, 02:01 AM

nofriends

Senior Water Wizard

Join Date: Aug 2004

Location: Cape Town, RSA

Posts: 10,186

Time spent in forums: 3 Months 2 Weeks 2 Days 7 h 36 m 24 sec
Reputation Power: 699

I am glad it worked,

the file is being read line by line, so if you now that the first line
is only a heading, then you can just skip the first line of the file.

It doesn't matter how many columns the csv file have, just as long
as you now which ones you want to save into the db

February 14th, 2006, 02:17 AM

DOLL

Contributing User

Join Date: Feb 2006

Posts: 32

Time spent in forums: 7 h 50 m 53 sec
Reputation Power: 3

Gracias senor....

#10

February 14th, 2006, 02:24 AM

nofriends

Senior Water Wizard

Join Date: Aug 2004

Location: Cape Town, RSA

Posts: 10,186

Time spent in forums: 3 Months 2 Weeks 2 Days 7 h 36 m 24 sec
Reputation Power: 699

no problemo

#11

February 14th, 2006, 07:56 PM

DOLL

Contributing User

Join Date: Feb 2006

Posts: 32

Time spent in forums: 7 h 50 m 53 sec
Reputation Power: 3

Senorr..
I have abit of a problem.

I thought all my CSV tab-delimited files are well..tab-delimited but when I check it again, it is not separated by tab-spacing. It was actually.......just...space-delimited. Gosh.. I have searched the entire Google but there ain't seem to have an answer for these space-delimited CSV files. Is there Senorr?

Can I replace the space to a tab? If so, what are the ways to write it?

#12

February 15th, 2006, 09:35 AM

Darius

Contributing User

Join Date: Sep 2004

Posts: 108

Time spent in forums: 11 h 16 m 54 sec
Reputation Power: 5

The traditional CSV is "," as a separator, in Windows "Control Pannel", "Regional Settings" and "Number" configuration of "List Separator" must be a "," not a tab.

For space the split(sline," ") or split(sline,chr(32)) could work.

#13

February 16th, 2006, 05:18 AM

DOLL

Contributing User

Join Date: Feb 2006

Posts: 32

Time spent in forums: 7 h 50 m 53 sec
Reputation Power: 3

Oh vo yer..But I've tried split(sLine," ") but it wouldn't work. There was an error:Subscript out of range : [-1] or so like that. And it was pointing towards the line >> split(sLine," ")
What could the error message means?

#14

February 16th, 2006, 09:48 AM

Darius

Contributing User

Join Date: Sep 2004

Posts: 108

Time spent in forums: 11 h 16 m 54 sec
Reputation Power: 5

The only way to have such error is if sline="", but the same for any other kind of separator.

so:

if sline<>"" then Line = split(sline," ")

#15