473,614 Members | 2,270 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

import text file in sql 2005

43 New Member
i have a text file. I want to import it in table in sql 2005.that table has 8 fields.whn importing text file first 4 fields are in first line and next 2 in 2nd and rest 2 in last line. I want all 8 fields in one line. plz guide.
Dec 1 '08 #1
2 2631
myct
4 New Member
Since you are using SQL 2005, once way (where you would have more control over the import) is to use write a CLR stored procedure, that will read the file using a StreamReader that loops through the file and treats each 3 lines (and multiples thereof) as a single record and inserts into the table.
If you go this way, remember you can use Context Connection = True to refer to the database that you are calling to CLR SP from...

Hope this helps
-Shiva
mycodetrip.com
Dec 1 '08 #2
ck9663
2,878 Recognized Expert Specialist
What's the text file type? Delimited? Fixed?

-- CK
Dec 2 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

1
2691
by: jean.rossier | last post by:
Hello All, I am facing a problem while importing pylab library(in a .py program file) via web browser however the same program works when I execute it from the command prompt. my configuration : Fedora Core 3 Apache 2.0
3
2143
by: Joost | last post by:
Hello, I am using the LOAD DATA INFILE command to import a text file into mysql. However, there is much more text in the file than needed in my tables. How can I make a selection from the text line. For example position 5 - 10 in field A, position 20-23 in field B, etc Regards,
2
3070
by: David Berry | last post by:
Hi All. I'm looking for any help or sample code that can show me how to make a file import wizard in ASP.NET (VB preferred) like the one that MS Access uses. I'm working on a web site where the user has the ability to upload a file (.txt or .csv). The data in the file may be comma delaminated, tab delaminated, fixed width etc (we don't know). What I'd like to create is something like MS Access uses to import an Excel file into the...
1
2828
by: Shaun Harwood | last post by:
Hello all Here is a puzzle I hope someone has a solution for. I am trying to import a delimited text file using a Visual Basic 'TransferText' command. So that the table is created properly I have an Import Specification set up and it seem to work ok. I have a new text file every day to load - when I have finished using the information in it - I have a macro which deletes the table. Here is my problem. I have set the specification so...
2
11052
by: musicloverlch | last post by:
I need to import 60 text files all in one folder. Can I use VBA to cycle through all the files, without having to import them one by one? Does anyone have an example? Thanks in advance. Laura
3
2631
by: cbanks | last post by:
I would appreciate some help on a procedure that I have. Using Bulk Insert, I would like to import records from a text file. The issue I have is the file contains a header - '1AMC_TO_Axiz' and a footer '1AMC_TO_Axiz2". Using a format file, I can get the import to work by editing the file and removing these two entries. Is there a way to setup the format file to skip these two entries? My file currently looks like this: 7.0 16 1...
10
2109
by: Avi | last post by:
Hi I need to read in a large set of text files (9GB+ each) into a database table based on fixed width lengths. There are several ways to complete this, but I am wondering if anyone has insight into the FASTEST way to read in the data and chop it up ahead of posting it into the DB. So far, things work, but they are far slower than expected.
3
3222
by: Davy B | last post by:
I am trying to import a data file, which is tab delimited, using BULK INSERT. I have used BCP to create a format file, since the destination table has around 20 columns, but the data file has only three. Here's the problem: The columns I am trying to import comprise ID (an int identity column), Name (a varchar(255) column and Status (a small int column). The data file contains identity values for the first column, so I am using the...
6
9973
by: EricR | last post by:
I am trying to bcp import a text file into a SQL Server 2000 database. The text file is coming out of a java application where order information is written to the text file. Each record is on it's own row, so the last item in each record has a new line character at the end of it to create the next row. This works well in creating the file however bcp does not like to import this text file with the extra blank line at the end. If I change...
9
21823
by: cabrenner | last post by:
I am new to SQL Server, and migrating part of an Access application to SSE. I am trying to insert a comma delimited file into SSE 2005. I am able to run a BULK INSERT statement on a simple file, specifying the field (,) and row (\n) terminators. I can also do the same with a format file. Here is the problem. My csv file has 185 columns, with a mixture of datatypes. Sometimes, a text field will contain the field delimiter as part of...
0
8180
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8623
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8576
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8275
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7091
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4050
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4121
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2566
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1423
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.