Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Stevan23
Starting Member
15 Posts |
 Posted - 2014-05-10 : 06:43:09
|
| Hi all,We're using Microsoft SQL Server 2008 and my MD has asked me to bring in some csv files from a shared computer into an SQL database. The csv files are comma delimited and contain double speech marks around each column and does not contain column headers. The program that makes these files can only have 30 records before it starts to delete the oldest (hence why my MD wants it on a database to preserve the history). The first problem is with the consistency of the records (the amount of columns vary even within one csv file).The second problem is in the table structure setup and whether I need to specify a unique key (which would be a sampleid number - unique to each row).The third problem is how to bring only the new records and ignore the duplicates.In regards to the problems, I have the following questions:1) We have a maximum of 7 measures to analyse using the program that generates the csv file (not all are used), do all these need to be switched on even if they are not being measured to allow:2) Standardisation of the table structure in SQL server and does a unique key need to be made to match a column in the csv file that identifies the row as unique?3) I am trying to import the new rows using the SQL data import and export wizard, but it keeps giving me an error regarding a duplicate key? I'm not sure if this is due to my table setup or if it is part of the wizard that blocks all duplicate data and hence the entire import process?I'm not bad with the SQL queries etc, but this is a bit of a new step. I've tried to google a few things and I'm still coming up short with this problem. I've seen that this can also be done via bcp or bulk import, but the data export and import sounds great as the package can be saved and run as part of the job agent. Any help would be greatly appreciated!Thanks,Stevan23 |
|
|
waterduck
Aged Yak Warrior
982 Posts |
Posted - 2014-05-11 : 20:54:31
|
| 1. The first problem is with the consistency of the records (the amount of columns vary even within one csv file).You might better off try with xml table. SSIS below 2012 cannot allow rows with different column count.2. The second problem is in the table structure setup and whether I need to specify a unique key (which would be a sampleid number - unique to each row).You need the unique key for the 3rd question you ask. 3. The third problem is how to bring only the new records and ignore the duplicates.You can look into merge syntax. |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2014-05-12 : 01:46:20
|
| Once you've established the table structure , with unique key etc - there are a number of methods you can use to import the data from the csv into the db table. Given the inconsistency of the data - some staging process may be required - which would make the amount of column counts consistent.A method I've incorporated with good levels of success is to use powershell as the import mechanism. This allows you to manipulate data effectively and can easily be incorporated in an ETL process - http://www.sqlserver-dba.com/2013/01/sql-server-export-excel-data-to-sql-server-with-powershell.htmlJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
Import csv file to SQL via import wizard