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 |
|
Hodgepodge
Starting Member
6 Posts |
 Posted - 2003-08-14 : 17:55:32
|
| Hello allThis is my first visit. I was referred here by the MS SQL XMLnewsgroup. I hope you'll be able to answer this question. I'm putting together a MS Windows SharePoint Service site with MS SQL Server as the back-end. I'm going to be using MS InfoPath as a front-end to complete some forms I've designed. As I'm sure you know, InfoPath's format of choice is XML.I've got data in the form of straight text (.txt). Its a couple of thousand names with other pertinent labeled info. Is there a way to import this text into SQL and add the same labels on-the-fly? Once this data is imported, query info will be used to complete additional InfoPath forms. Being able to add info will be another function of this SQL database.TIAWil HodgeHow do I make this work! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-14 : 17:57:58
|
| To import data from a text file, you could use DTS (either the designer or the wizard), or you could use bcp. What format is the file in? Is it delimited (what is it delimited by) or is it fixed? Is there a carriage return and line feed at the end of each row or just one of them? These are things that bcp and DTS will need to know in order to import the data.Tara |
 |
|
|
Hodgepodge
Starting Member
6 Posts |
Posted - 2003-08-15 : 14:34:17
|
quote:
Originally posted by tduggan
.... What format is the file in? Is it delimited (what is it delimited by) or is it fixed? Is there a carriage return and line feed at the end of each row or just one of them? These are things that bcp and DTS will need to know in order to import the data.Tara
Hi tdugganThanks so much for your reply!  The text file is not delimited, just a fixed .txt file written in Notepad. There are carriage returns at the end of each labeled line, and eachone of these sections is separated by asterisks (*********).If you have any further questions, please ask.And thanks again for the help!WilHow do I make this work! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-15 : 15:12:41
|
| The asterisks are going to give you a problem. You would have to remove those in order to use bcp or DTS. Could you provide a sample of the records?Tara |
|
|
|
Hodgepodge
Starting Member
6 Posts |
Posted - 2003-08-15 : 18:02:25
|
quote:
Originally posted by tduggan
The asterisks are going to give you a problem. You would have to remove those in order to use bcp or DTS. Could you provide a sample of the records?Tara
Hi again Tara (I assume we're on first name basis now), Your initial reply forced me to do a lil search, and low-and-behold I found a place called http://www.sqldts.com. Now I'm sure you're quite aware of this particular site, but it gave me a nice tutorial to read.In so doing, I came across my first error in assumption. My text file is delimited and not fixed. I assumed delimited meant something other than it did in this particular case. I haven't read the whole tutorial yet, but it seems to have promise.Now to your second iteam. Here's how the records are laid out:"Title in quotes" - some info here - here - and here (no labels, but hyphiens are used; all on the same line; carriage return)Label: Info here (carriage return)Label: Info here (carriage return)Label: Info here (carriage return)Label: Info here (carriage return)Label: Time info here (e.g. 200 min.; carriage return).Label: (Nothing here; two carriage returns)Name......................................TitleName......................................TitleName......................................TitleLabel: (Nothing here; two carriage returns)(Text here that might take up a few lines with carriage returns or not).**************************************************************Then it goes on to the next record. I'd actually like to add some additional info to each record, but I'm not sure whether it would be better to wait until its in SQL or add it to the text records?Any of the above can and will be changed to make this an easier process, so take that into consideration.Well, what do you think?WilHow do I make this work! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-15 : 18:21:25
|
Well data files typically look something like this:1,"Rob",7123,42,"SQLTeamer"2,"Nigel",4563,42,"SQLTeamer"3,"Merkin",2744,42,"SQLTeamer"4,"Graz",2123,42,"SQLTeamer"The above example is comma delimited with double quotes for the text qualifier. Each row represents a record that is to be placed in a table. Each element represents a column. The comma is placed in between the elements.The way that you have your data in the file is not something that bcp or DTS is going to be able to handle. Do you have any tables that have data in them? If so, let's run bcp so that you can see what the data should look like. Open up a cmd window. Navigate to the directory where bcp.exe exists (search the database server or a machine that has the SQL Client Tools installed for the exe). Then do this:bcp DatabaseName.dbo.TableName out c:\temp\SomeFile.txt -SServerName -USomeUser -PSomePassword -c -t, -r\r\n Modify DatabaseName to the name of the database where the table exists. Modify TableName to the name of the table, etc...Run the command and then open up the file, such as C:\temp\SomeFile.txt. This is a sample file. It is comma delimited with no text qualifiers. Each row ends with a carriage return and a line feed.Tara |
|
|
|
Hodgepodge
Starting Member
6 Posts |
Posted - 2003-08-18 : 17:54:45
|
Answered in line and BOLDquote:
Originally posted by tduggan
Well data files typically look something like this:1,"Rob",7123,42,"SQLTeamer"2,"Nigel",4563,42,"SQLTeamer"3,"Merkin",2744,42,"SQLTeamer"4,"Graz",2123,42,"SQLTeamer"The above example is comma delimited with double quotes for the text qualifier. Each row represents a record that is to be placed in a table. Each element represents a column. The comma is placed in between the elements.That's how I've seen that word delimited, with the addition of the word coma.The way that you have your data in the file is not something that bcp or DTS is going to be able to handle.Boy, am I sorry to hear that. Do you have any tables that have data in them? If so, let's run bcp so that you can see what the data should look like. Open up a cmd window. Navigate to the directory where bcp.exe exists (search the database server or a machine that has the SQL Client Tools installed for the exe)....Our SQL was installed because it was required by Windows SharePoint Services. We had a choice between it and MSDE and went with SQL because of the database we wanted to produce. So, we have no Tables or Records per se. Can I assume we'll have to start from the beginning and produce the database from within SQL? At least we'll be ableto make the necessary changes and additions in the initialdesign phase.Wil
How do I make this work! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-18 : 17:59:04
|
| I don't understand this statement:"That's how I've seen that word delimited, with the addition of the word coma."I also don't know what this means:"Can I assume we'll have to start from the beginning and produce the database from within SQL? At least we'll be ableto make the necessary changes and additions in the initialdesign phase."What do you have so far? Just a file? Is this file representative of a table in a database or would it be representative of multiple tables? If it's multiple tables, then you're gonna have to break them apart so that one file represents one table. But there's still the problem of the format of the file.Tara |
|
|
|
Hodgepodge
Starting Member
6 Posts |
Posted - 2003-08-18 : 23:26:17
|
quote:
Originally posted by tduggan
I don't understand this statement:"That's how I've seen that word delimited, with the addition of the word coma."In one of your previous posts you mentined was our text file fixed or delimited. I had heard the word before but under the context coma delimited.I also don't know what this means:"Can I assume we'll have to start from the beginning and produce the database from within SQL? At least we'll be ableto make the necessary changes and additions in the initialdesign phase."Maybe I can answer this and the next question together.MSDE or SQL Server are requirements to install WindowsSharePoint Services. We decided to use SQL because wewanted to setup this elaborate database. We've made NOchanges to SQL other than install SQL. I thought if we could import our text file we would eliminate the need to desigen a database from scratch and manually enter the text file data. What do you have so far? Just a file? Is this file representative of a table in a database or would it be representative of multiple tables? If it's multiple tables, then you're gonna have to break them apart so that one file represents one table. But there's still the problem of the format of the file.Tara
How do I make this work! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-08-19 : 14:29:35
|
| You have to build a file that an import/export program can use. Where is this data coming from?Tara |
|
|
|
Hodgepodge
Starting Member
6 Posts |
Posted - 2003-08-19 : 18:18:16
|
quote:
Originally posted by tduggan
You have to build a file that an import/export program can use. Where is this data coming from?Tara
I started this text file some years ago.It was just a way to keep some type ofrecord of certain infomation. There wasn'ta reason at the time to put it in databaseform. Boy am I sorry there wasn't a lot offoresight involved.  I'm going to take your advice and build one of the records into a coma delimited file. Would you advise using the tutorial I found at SQLdts.com to import the rebuilt record into SQL? If the experience is not fraught with a lot of problems and/or time involved, maybe thatwill be the way to go instead of designing the database and manually inserting the data.Thanks for all your time and knowledge Tara.I'll keep you advised of the goings on. I'msure I'll run into other problems along theway, I'm just glad now I have a place to take them.  Thanks again to you and SQLteam!WilHow do I make this work! |
|
|
|
|
|
|
|
|
WSS, InfoPath and SQL