Solved

Importing CSV File into the SQL Server

Posted on 2014-01-08
5
1,426 Views
Last Modified: 2014-01-09
I want to import the CSV file into the database using the file I uploaded!  The two columns of the data I wanted will be starting from Cell A12 to B12 and down to A27 and B27.  The top portion of the CSV file will be ignore.  I was wondering if anyone done something similar to this?CF20.csvI want to import the CSV file into the database using the file I uploaded!  The two columns of the data I wanted will be starting from Cell A12 to B12 and down to A27 and B27.  The top portion of the CSV file will be ignore.  I was wondering if anyone done something similar to this?
0
Comment
Question by:eli411
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 400 total points
ID: 39766993
You can open the file with Excel and remove the information that is not required. I recommend you first delete the rows after 27, and then rows before 12. Also, delete columns after column B. Your csv file will then look like this:
Sanitised dataThen save the file as csv and just use bcp utility to import the file, like this:
bcp MyDatabase.dbo.MayTable in -i c:\temp\cf20.csv -t ","

Open in new window

0
 
LVL 13

Assisted Solution

by:Koen Van Wielink
Koen Van Wielink earned 50 total points
ID: 39767305
Chaau is correct about cleaning up the CSV before importing.
After that, you can alternatively also use the import wizard in the SQL management studio to import the file. Right click on the database name, then select Tasks - Import data. Indicate your data source (flat file for CSV or, if you want to make it a bit simpler, save the csv file as an Excel workbook and select Microsoft Excel as data source). Check the other settings and verify if anything needs changing. On the next screen, indicate your data destination.
When you get to "Select source tables and views" you can have the wizard create a table for you (default), or select an existing table. Always check the mapping if you import into an existing table. After that click next until you can execute the package, and the data should be imported.
0
 
LVL 10

Assisted Solution

by:Ramesh Babu Vavilla
Ramesh Babu Vavilla earned 50 total points
ID: 39767374
check this other optino of importing
http://blog.sqlauthority.com/2012/06/20/sql-server-importing-csv-file-into-database-sql-in-sixty-seconds-018-video/

Open in new window

http://blog.sqlauthority.com/2012/06/20/sql-server-importing-csv-file-into-database-sql-in-sixty-seconds-018-video/
0
 
LVL 2

Author Comment

by:eli411
ID: 39768767
Thanks guys!  I was trying to find a way to import the data so some of those administrative people do not have to clean up the CSV file.  I guess that's the only option they got then!
0
 
LVL 13

Expert Comment

by:Koen Van Wielink
ID: 39769772
Where do these csv files originate from? Are they exports from another system? If so, perhaps you can change the way the csv is generated.
Alternatively you might be able to do something with macros/VBA in Excel, where a bit of code would first insert the csv content in a structured Excel template before you import it to SQL. Not my area of expertise but others on this forum know.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

717 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