473,692 Members | 2,141 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Import ASCII Data

I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the data
into tables.

What I would like to do is create an automated import function in SQL.

I am new to SQL, can anyone point me in the direction I should look to find
out how I could perform this task?

Using SQL 2005.

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.sqlmonster.com

Aug 29 '06 #1
2 6365
mattc66 via SQLMonster.com (u16013@uwe) writes:
I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data into tables.

What I would like to do is create an automated import function in SQL.

I am new to SQL, can anyone point me in the direction I should look to
find out how I could perform this task?

Using SQL 2005.
There are a couple of alternatives. There is BCP (command-line tool)
and BULK INSERT (T-SQL statement) which work very similarily. Their
good as long as the files have one entry for each file in each record,
and there are no headers.

You can use the Import Wizard in SQL Server Management Studio. As with
all graphical tools, it's good for a one-off, but it's really a good
place if you need to do this on a regular basis. The Import Wizard requires
that SSIS (see below) is installed.

And then there is SQL Server Integration Service (SSIS), the member of the
SQL Server family that is all about importing and exporting data and
transforming it on the way. I have not used SSIS or its predecessor
myself, so I don't really know what it's so fantastic. (Being an old-
timer, I get by very well with BCP and BULK INSERT.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 29 '06 #2
To add to Erland's response, you can use a SQL Agent job to schedule the
import process. In the case of a package created with SSMS or the BI dev
studio, there is a specialized SQL Server Integration Services step type
that allows you to specify the desired package and run time settings via a
GUI.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"mattc66 via SQLMonster.com" <u16013@uwewrot e in message
news:65826d5d02 090@uwe...
>I have data that comes from a legacy system. I can obtain the data in an
ASCII format. Currently I have created scripts in ACCESS to import the
data
into tables.

What I would like to do is create an automated import function in SQL.

I am new to SQL, can anyone point me in the direction I should look to
find
out how I could perform this task?

Using SQL 2005.

Thanks
Matt

--
Matt Campbell
mattc (at) saunatec [dot] com

Message posted via http://www.sqlmonster.com


Aug 31 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2262
by: Bethany Holliday | last post by:
Hi all, I have a file with an extension of .sdf. I "believe" it is a text file of some sort but I am uncertain. The source agency hasn't returned any of my calls so I'm wondering if anyone is familiar with this extension? I'd like to import the file into my database - when I use DTS and chose a text format, regardless of what delimiter I choose, the format is still really ugly. when I pull it up in a huge text editor, it is hard for...
1
3207
by: Richard Holliingsworth | last post by:
Hello: Thanks for your quick response. I'm trying to import a new Excel file into an A2K table and it's truncating the data. One of the Excel columns is a text field that can be up to 2000 characters. When I import it into A2K, that field is truncated. If I try to inport the file into an EXISTING table that I define myself where that field is a MEMO field, the import crashes.
0
345
by: Joe Fallon | last post by:
I am importing a delimited ASCII text file into a dataset using OLEDB. I set up the connection and dataadapter and then fill the dataset. It works - sort of. It turns out that some of my data is omitted. Specifically, one of the columns is set to Int32, but the data is really string. The first few rows of data have numeric valus in this field but further down there are some letters mixed in with the numbers. So only fields with pure...
5
2179
by: mark_galeck_spam_magnet | last post by:
Hi, why does complain name 'compileFile' not defined. But works. Why? (I did read the tutorial, it seems to say "import module" should work. Thank you, Mark
3
6949
by: anitak | last post by:
Hi, I would like to know if Oracle provides any feature to import data in ASCII/XML files directly. I would not wish to go for Oracle import in Binary format. I found such provision provided by MYSQL.Please refer the below URL for further details. http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html
5
3367
by: Gregor KovaĨ | last post by:
Hi! I have a ASCII file that I need to import with METHOD L, since columns are fixed length. The problem is that I have more columns in a table that I'm importing to that there are columns in the file. Is it possible to specify some default values for columns that are not part of ASCII file? I'd like that default value to be used in INSERT_UPDATE import option. Best regards, Kovi
6
3087
by: Hemant Shah | last post by:
Folks, Today, I was exporting a table in one database and then importing it in another database. The table in destination database was missing one column (my mistake while creating the table), but import did not complain about it. Source table: Column Type Type
1
3738
by: crs27 | last post by:
Hai All, I want to import data from Oracle 10g to POstgreSQl 8.2.Would like to know if their is any way to import data from pgAdmin III. Thought of using Copy command,but for this the files needs to be either in Ascii or Binary formate. Kindly suggest me any means of importing data from oracle to postgres. Thank You
9
2114
by: rsoh.woodhouse | last post by:
Hi, I'm trying to work out some strange (to me) behaviour that I see when running a python script in two different ways (I've inherited some code that needs to be maintained and integrated with another lump of code). The sample script is: # Sample script, simply create a new thread and run a # regular expression match in it. import re
0
8604
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
8961
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
8800
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
7627
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...
1
6459
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4557
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2974
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
2
2238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1957
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.