Greetings! I hope some can help with out with this. I am trying to write a scripting using bcp to import large amounts of data from a text file that is tab delimited. The problems I have run into is that when bcp does import the from the file, it will only import 8 lines, then stop. Opening the text file reveals that it contains characters like / \ , ' " . and all sorts of messy things. Looking at the table after importing the 8 lines also shows that the table is not going into the rights places. For example. If I had the phrase "The quick brow fox jumped over the lazy dog" Assume that every two words should go into a column. ie. "the quick" "brown fox" "jumped over" "the lazy" "dog" It would produce. "the qu" "ick brown fox" "jump" "ed ov" "er the lazy dog" instead. Due to the nature of the information I cannot give exact examples. So my question is. What am I doing wrong? Is there anyway escape those characters without editing the text file? Other people outside of our company create the files and won't be willing to change how the do things. Any ideas will be appreciated. Thanks.
5  7154  ck9663 2,878
Recognized Expert Specialist
Check the structure of your .FMT (format) file that you are using. If the structure is too small, it will definitely spill over to the next field. Also, try and check your text qualifier. Try uploading it using the Import Wizard first and see how the sql server treat each field.
-- CK
I've tried to use the import/export wizard and here are my results. When using the wizard to import the data I have columns that don't have anything in them and the data that should go in them is in the next column. This happens at several places. I can try to add filler columns but then I have extra columns at the end and it doesn't like that either. Help!
ck9663 2,878
Recognized Expert Specialist
Do you have a header?
-- CK
sorry it keeps taking me so long to reply, for some reason I'm wasn't getting notification that there are new messages (I am now). Anyway, yes I do have headers. I have since spoken (again) with the tech guys on their end of things, and they (the company) have changed us over to a mixed delimited file. Now the file is both tabbed and | (piped) delimited. Confused yet? It gets better. After they switched us over, I have setup new columns with new headers and the new file. It still doesn't work. I have a feeling this is their problem and not mine. Viewing the text file shows that while some rows have 20-30 pipe separated columns the row below may only have one or none at all. I don't think the company realizes what this does to the people trying to get this to work.
Thanks for the help you have given me. I have, for the moment come up with a work around for my problem. I have decided to leave all of the pipe columns in one column instead, and use substring in whatever programming language we decide to use give us our individual values. Our next problem will be making a page load quickly while having to process several if then statements.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Philip Boonzaaier |
last post by:
I want to be able to generate SQL statements that will go through a list of
data, effectively row by row, enquire on the database if this exists in the
selected table- If it exists, then the colums must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values...
|
by: DCM Fan |
last post by:
Access 2K, SP3 on Windows 2K, SP4
All,
I have an import spec set up with quoted Identifiers and comma-separated
values. The text file is produced by a 3rd-party program of which I have no
control. It outputs all text fields surrounded by quotes, and all numeric
fields w/o quotes. All fields are separated with commas.
This has been working for 2 years, until today, when one of the data fields
|
by: mark |
last post by:
In Access 2000 and 2002, I have created an import specification to import the
fixed-width recordset below into an existing table. I am having strange
problems with the import of the date and time fields.
177 102003 16:43:12 102003 18:43:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
165 102003 17:43:12 102003 18:44:12 6OAG0ADP Y 0000 0000 0000 0000 61930 4HGA800
177 102003 16:41:18 102003 18:45:12 6OAG0ADP Y 0000 0000 0000 0000 61930...
|
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...
|
by: Ted |
last post by:
OK, I tried this:
USE Alert_db;
BULK INSERT funds FROM 'C:\\data\\myData.dat'
WITH (FIELDTERMINATOR='\t',
KEEPNULLS,
ROWTERMINATOR='\r\n');
| | |
by: Shawn Ferguson |
last post by:
With the help of you, I've been able to successfully load my database with the contents of a "comma" delimited file. It loads about 5000 records in about 3 seconds:) Now I have another problem. The file I will be importing into SQL Server is serparated by "|" vertical bar opposed to "," comma. When I run my program with the comma separated text file, works great, but when I try to use the same process to import the | (vertical bar) it only...
|
by: Pete |
last post by:
I need to import multiple tab delimited files for which I do not have
the formats. All files have column headers in the the line. The
files must be dynamic. The only common value is that the files are
text and use tabs to delimit the column data.
I can not use the File/Import as people using the program do not have
the necessary experience to perform this function. Therefore, I need
to programtically create unique tables for each...
|
by: ozkhillscovington |
last post by:
We have sp's in place that do BULK INSERTS from txt files into the tables. This works fine, however they have asked us to add a field that identifies accounting ctr. The only thing that identifies accounting ctr is the last three letters of the text file being used for the BULK INSERT.
How would you suggest that I do this? Is there a way to add a default value to the .fmt or schema files for the text fields, or in the BULK INSERT...
|
by: yeoj13 |
last post by:
Hello,
I have a db2load script I'm using to populate a large table.
Ideally, my target table is required to have "Not Null" constraints on
a number of different columns.
I've noticed a huge performance hit when I load the target table with
"Not Null" constraints as compared to loading a target table without
the constraints.
|
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...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
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...
|
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,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
