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

Posted on 2005-04-07
Last Modified: 2006-11-18
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:


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

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
-----------------End Code

I hope i'll get a good and quick solution for this problem.
Question by:aceinfotech
    LVL 25

    Accepted Solution

    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:

       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.;en-us;187670
    LVL 11

    Assisted Solution

    Try adding quotes around the IP Address in the source file

    LVL 9

    Expert Comment

    use the DNS name instead :)

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


    Author Comment

    I have the format as
    LVL 9

    Expert Comment

    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.

    LVL 25

    Expert Comment

    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.
    LVL 25

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now