CSV to Access

[Bob Barrows [MVP]]

CJA wrote:

> Hi
>
> I have a very simple script that seems to work OK inserting into an
> Access table from a CSV file. The problem I have is that the first
> column (stored as text) has student ID numbers - some start with
> 100... others start with a character like K or M. It inserts the
> 100... IDs but not the rest. Anyone any ideas?

What are the symptoms? Do you get any error messages? Could it be attempting
to insert text into a numeric field?

> Here is the code:
>
> set objConn= Server.CreateObject("ADODB.Connection")
>
> strDBPath = Server.MapPath("../DB/Live.mdb")
> objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
> strDBPath & ";"
>
> Dim csv_path
> csv_path = Server.MapPath("./Uploads") & "\"
>
>

Why do you assign this text to the SQL variable:

> SQL = "SELECT * INTO Learner FROM [Learner2.csv] IN """ &
> csv_path & """ ""TEXT;"""
>

and immediately overwrite it with this text?

> SQL = "INSERT INTO Learner SELECT * FROM [Learner2.csv] IN """ &
> csv_path & """ ""TEXT;"""
>

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[CJA]

Hi

I have a very simple script that seems to work OK inserting into an
Access table from a CSV file. The problem I have is that the first
column (stored as text) has student ID numbers - some start with 100...
others start with a character like K or M. It inserts the 100... IDs but
not the rest. Anyone any ideas? Here is the code:

set objConn= Server.CreateObject("ADODB.Connection")

strDBPath = Server.MapPath("../DB/Live.mdb")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDBPath
& ";"

Dim csv_path
csv_path = Server.MapPath("./Uploads") & "\"


SQL = "SELECT * INTO Learner FROM [Learner2.csv] IN """ & csv_path &
""" ""TEXT;"""

SQL = "INSERT INTO Learner SELECT * FROM [Learner2.csv] IN """ &
csv_path & """ ""TEXT;"""

'Executing the SQL Statement
ObjConn.Execute SQL

Hope someone has the answer.

CJA

[CJA]

Hi Sorry about the cross-post. I am new to this.

I don't get any error messages. It just leaves the field blank if it comes
across an ID with a character at the begining. This is simply a bit of
script I found elsewhere. It works fine on other files. The field it
inserts into is text, so it should be OK.

CJA