Databases :: Import From Csv To SQL With >255 Columns?
Nov 12, 2010
I'm writing a reporting tool that, as a starting point, needs to create a DB table from a csv file. This csv file will have been created from exporting data from our old system (with the new system being the reporting tool) and can have up to 300 columns of data. I've been trying as follows:
but am finding that only the first 255 columns get put in. I am assumg that the Jet driver is the one that is limiting the columns, or is there a registry setting somewhere that'll solve it quickly?
Is there another approach that'll work? I've though of the following, but as it'll take a while to recode and test each time I thought I'd ask the experts first!!
BULK INSERT: would mean that I'd need to pre-create the table first, but if the issue is with the Jet driver I'd surely get the same problem? Use a datatable: it seems pretty fiddly and probably a lot slower, but I guess I could read into a dataset and then bind that to a table to insert? Again the table would need to exist and the driver that I use to read from the csv would be the issue? Do it one row at a time in a loop in the code: assuming I can read the file as text, I could then split the data in code into an array and create a insert statement (again to an existing table) for each row. With what could be 1000s of rows I can't imagine that this is the fastest solution!
Not that it should matter.. the 2nd value in the csv is an IP address.. its in the file as 192.168.7.25(just an example), but gets read as 192.1687, if the file is setup as delimited in the connection string, does that have any restraints on the data being read? When i open the file in notepad, the comma is at the end of the IP address, when i open it in excel it is in its own column.. so what could be causing this? Because of this issue with the truncation, that is what is causing the primary key issue.. because there are other values that are similar like these:
192.168.7.25 192.168.7.26 192.168.7.45
because if the issue, it passes each one of those as 192.1687 Here is the table structure, in case it helps resolve this issue.
[id] [int] NOT NULL, [IpAddress] [varchar](16) NOT NULL, [MacAddress] [varchar](16) NULL, [APTypeId] [int] NOT NULL, [APUser] [varchar](256) NULL, [APPass] [varchar](128) NULL, [APLocation] [varchar](20) NULL, [APFreq] [varchar](50) NOT NULL, [APBand] [varchar](50) NOT NULL,
i have some excel file which export from sybase, but it can only export to Excel ver2.1.then I connect and import to SQL Database. The result is error and prompt cannot read the Excel format.Finally I try use Excel 2003 to open the exce v2.1 and save as 2003 version. Then import again, it can success to import to SQL database.my connection string excel properties = 8.0i tried to change 8.0 to 2.1 or 5.0, the result is still cannot import. how to import v2.1 directly. no need use Excel to open and save again.another question, if I want import more than one excel, how can I import all one time only.
I am pretty good with HTML, PHP & MYSQL but this is my first go at working with a windows host and an ASP script. I recently purchased a script that matches my clients needs and when i have opened the folder to view the files its all double dutch to me apart from one css files which makes perfect sense to me!
Im stuck with is the database file included in the template, i ave been unable to import it into MSSQL using 'My Little Admin' which is the control panel provided by my hosting provider. Looking through the file with notepad it seems to contain the information for several tables but i cannot figure out for the life of me how i import it with 'My Little Admin'
I am developing a website admin control page....I have to import data from an excel sheet to a table...the table same columns as excel sheet....how do i import data from excel sheet....also i have to make every record unique by giving a unique ID.
im using sqlbulkcopy to import all the columns and rows in an excel file to a database. My question is, if my database has 1 more extra column which i have to map it to a session variable and that extra column in the database is not found in the excel, isit possible to do something like dat?
I am using PL/SQL Developer.I have a below query select distinct
DATA_SRC_CD as Source, FILE_NM as File_Name, PATH as File_Path from PKG_SFI_REPORTS.DATALOAD2SUMMARYETLRPRT("FACTS","CM_DDL_PARTY_2011_01_12_08_24_09.DAT") In the above Query PKG_SFI_REPORTS => Package, DATALOAD2SUMMARYETLRPRT=> Procedure. "FACTS" and "CM_DDL_PARTY_2011_01_12_08_24_09.DAT" => Parameters Now I need to know the Data Types of "DATA_SRC_CD" and "FILE_NM" and "PATH". How can I Know?
i did the mysql data export into excel in row wise when button click... here i want the same data export into columns wise..i used datatable and dataset for this function.. here i need all data should export in column..here i used some code..
On one of our tables, a default clustered index is defined on the primary key by sql server 2005. The table also contains forign key columns and has got over 2 million rows. There are very frequent INSERT, UPDATE and DELETE functions on this table. Recently some of the SELECT queries which primary operate on the forign keys have become very slow (resulting in application timeout). To fix the problem, I defined an index on one of the primary keys which gave me very quick results. I don't know much about indexes.
1. Since I defined a new index (on the forign key) in addition to the existing default index, what kind of index (clustered or non-clustered) have I defined?
2. What performance impact does it have on the rest of the system? Is it going to break something else?
3. I read about fill factor. What is this and how does it effect the system?
4. Is the new index physically going to make any changes?
5. In case I delete this index later on, will the database return to the existing state or it will be a different state from now?
I have a page within our site that allows admin users the ability to upload configuration settings into the system instead of submitting 1 at a time.
While testing yesterday, i found that one of the upload pages allowed me to upload a excel file that had alot more columns than expected and when i checked the database, the first few columns that were expected took the values from the file, and everything else was ignored, but wanted to see if there is any way to check the number of columns in the file to make sure it matches the upload logic before accepting it.
True) to update database row that returns null values from date and numeric columns,Whenever i try to update same row with null (or empty) values on both date and numeric columns, it results in sending '01-Jan-0001' for date column and 0 for numeric column. I want the user to have the options of null or corresponding values for the update.