• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • Last Modified:

Not able to fully insert IP address from CSV file to MS SQL Server

I am facing a problem while inserting data from CSV file to MS SQL Server Table. Same thing I am doing From Excel file and its working fine.
-----------------------
My CSV file format is like this:

FirstName,LastName,Email,IP

Now when I am reading IP address from this CSV file using select query its taking as
111.1111
222.2222

and not the full IP address.
I think CSV is taking this as float. I tried using convert, cast  but it's not working and the code I am using to read is as below:


---------------Start Code
Dim strCSV as string
Dim upRS as Object
strCSV = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="+mypath+";Extensions=asc,csv,tab,txt;Persist Security Info=False;"
upRS = Server.CreateObject("ADODB.Recordset")
upRS.ActiveConnection = strCSV
upRS.Source = "select FirstName,LastName,Email,IP from " & request("F")
upRS.CursorType = 0
upRS.CursorLocation = 2
upRS.LockType = 3
upRS.Open()
-----------------End Code

I hope i'll get a good and quick solution for this problem.
0
aceinfotech
Asked:
aceinfotech
2 Solutions
 
jrb1Commented:
Maybe a quick solution.  Create a file called "schema.ini" in the same directory as the input file.  Edit this with a text editor and make it look something like this:

   [FILENAME.CSV]
   ColNameHeader = False
   Format = CSVDelimited
   CharacterSet = ANSI
   Col1=ProductID short
   Col2=ProductName char width 30
   Col3=QuantityPerUnit char width 30
   Col4=UnitPrice currency
   Col5=Discontinued bit

Make sure the IP address column is defined as a char.

http://support.microsoft.com/default.aspx?scid=kb;en-us;187670
0
 
pcsentinelCommented:
Try adding quotes around the IP Address in the source file

regards
0
 
solution46Commented:
use the DNS name instead :)

Seriously, what format is the IP address in? I'm assuming 11.22.33.44 but I just want to check you've not got some other format going on...

s46.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
aceinfotechAuthor Commented:
I have the format as 203.168.5.89
0
 
solution46Commented:
Hmmm. I guess pcsentinel has the best idea then- wrap the IP address in ""s. This will force SQL Server to treat it as text.

s46.
0
 
jrb1Commented:
Did you trying creating a layout definition to tell the ODBC the format?  Certainly adding quotes to the data source would work, but it shouldn't be that hard to define the data either.
0
 
jrb1Commented:
The schema.ini file is the way to format columns from a text file through the ODBC. That should have worked, as should have pcsentinel's answer...if that was possible.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now