Need suggestions on text file parsing into database

I have a website, where people upload tab delimited text files of their product inventories, which the site parses and inserts into a database table. Here's the catch: Instead of insisting that each user use a standardized format, each user can upload the file in whatever column order they want, they just have to let the site know through a GUI which column is in which order. And, they may upload columns that if not mapped, will be ignored. Right now, I am doing all of this in code and it runs slow, I was thinking of offloading this to either a stored procedure, ssis, or bulk upload. But, with the varying format of the uploaded text file, I am not sure how I could do that. Any suggestions?

Thanks!

Hi keggdirdle,

As far as I can see, we cannot make the database recognize tab delimited text files automatically.

Whatever technology you use (stored procedure, ssis, or bulk upload), they all depends on fixed format for data.

In this case, I don't think it is possible without your own code.

Need suggestion for BACKUP and Database files on RAID 5 configuration....!

I have RAID 5 with 1 logical drive with 2 partitions with SQL Server 2000 running on SERVER.

what best model for data storage and backup would some experienced one suggest:

basically I want to put Master, Database files and T-Log files away from each other.

-- Data files
-- Log files
-- Master database.

Thanks,

Fahim.

1.For data storage you can put in first partition the SQL software and in second the data, depends on the size of each ;in fact doesn't matter the configuration of data because the availability is supplied by RAID 5, more for same reason you can put all user and master databases in same place !

2.For backup you must storage the data out of this server using tape cassetes or a hdd in the network.

Need Select Query

Hi to all
I Need a sql Qery for Text files.
Fist I want to know will it be possible to select the All Filelds fromText file...?
I just thoght of like
Select * from MyTextFile.Txt
Please Help me.to my knowledge there isn't a way to do this via Transact SQL. If you have access to the xp_cmdshell stored procedure yo umight try something like:

Code:
------------------------------
master.dbo.xp_cmdshell 'type MyTextFile.Txt'
------------------------------

Of course you will need to fully qualify the path to the text file as SQL server would assume (based on the above) that MyTextFile.Txt is on drive C: of your SQL server.|||I just tried like this
master.dbo.xp_cmdshell 'type C:\Sample.Txt'

I got the message as follows

outpur
The system cannot find the file specified.
NULL

(2 row(s) affected)|||again, xp_cmdshell will look on your sql server for the file NOT your local PC. Try this and see what you get:

code:
------------------------------
master.dbo.xp_cmdshell 'dir'
master.dbo.xp_cmdshell 'type c:\boot.ini'
------------------------------|||Thanks a lot
It is workig fine..

need rldblib.lib, llibca.lib,dbdecdec

any body have three files below:
rldblib.lib, llibca.lib,dbdecdec.exe
that file use library clipsql,
using to access sql server under clipper aplication.
please help me...
thank before
Supratman - BogorI think these files last shipped on the SQLSVR 7.0 CD. If you can find an old copy of one you should be able to get the files.|||No, there are files with those names, but they won't help for a Clipper application. Clipper applications are 16 bit MS-DOS applications, they will require the 16 bit versions of those files that shpped with SQL 4.21a.

The quickest way to get those files is probably to open a support incident with Microsoft. If you run into any trouble getting the Microsoft support folks to understand what you need, let me know the SR number and I'll call in to explain it for you.

-PatP|||the last file is dbdecdec.exe|||the last file is dbdecdec.exeTrue, but that is for a network topology that has been dead for more than a decade... The company that created/used it has been bought up and dismantled.

If you truly need support for Pathworks, you have real problems because SQL Server hasn't supported that since at least version 6.5, and probably earlier than that. I know that Microsoft doesn't support it, and I seriously doubt that anyone else does.

-PatP

need programmer to build a DTS...

I need to get a dts package built in sql 2000. Basic tasks are:
* Users login to site and upload excel files to their own directory
* Need to validate the data within the excel files
* Then import that data into an existing database
* If a record already exists in db, then update it with new data
* original files to be zipped and moved to another folder after import
* exceptions logged or emailed
I haven't built a dts before and am looking to hire someone to build it
out. Usually go to elance but thought I could get some direction here...Hi
Read some info
www.sqldts.com
<dan@.globenettravel.com.au> wrote in message
news:1152108021.520182.221220@.a14g2000cwb.googlegroups.com...
>I need to get a dts package built in sql 2000. Basic tasks are:
> * Users login to site and upload excel files to their own directory
> * Need to validate the data within the excel files
> * Then import that data into an existing database
> * If a record already exists in db, then update it with new data
> * original files to be zipped and moved to another folder after import
> * exceptions logged or emailed
> I haven't built a dts before and am looking to hire someone to build it
> out. Usually go to elance but thought I could get some direction here...
>

need original disk for sp1 install? please tell me i don't...

hello
installing sp1 requires the original cd? please tell me i don't... i really
don't want to shuffle tons of files around so i can extract a 600mb file
just to install a service pack.
man, i hate this about msft... i'm always digging up original cds for
updates. what a pain. what's the reason for this?
dushan bilbijaPerhaps they're trying to discourage piracy?
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Dushan Bilbija" <dbilbija@.msn.com> wrote in message
news:%235YheoujEHA.2908@.tk2msftngp13.phx.gbl...
> hello
> installing sp1 requires the original cd? please tell me i don't... i
> really
> don't want to shuffle tons of files around so i can extract a 600mb file
> just to install a service pack.
> man, i hate this about msft... i'm always digging up original cds for
> updates. what a pain. what's the reason for this?
> dushan bilbija
>|||We did our best to ensure you would not need the original media when
installing SP1. Windows installer has some quirks about it that can make
this a difficult goal to achieve. I have seen this issue happen when config
files are copied over top of existing config files. The modified dates and
create dates confuse the installer and make it think it needs to recreate
the file.
If you send me the setup log file I can see if this is an issue we can
resolve for our next service pack.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:uvzPn1ujEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Perhaps they're trying to discourage piracy?
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "Dushan Bilbija" <dbilbija@.msn.com> wrote in message
> news:%235YheoujEHA.2908@.tk2msftngp13.phx.gbl...
> > hello
> >
> > installing sp1 requires the original cd? please tell me i don't... i
> > really
> > don't want to shuffle tons of files around so i can extract a 600mb file
> > just to install a service pack.
> >
> > man, i hate this about msft... i'm always digging up original cds for
> > updates. what a pain. what's the reason for this?
> >
> > dushan bilbija
> >
> >
>|||you'd think that was the reason... but how would they achieve that with the
original cd? a nice cd burner takes care of that problem. a quick google
search gives you any cd key you need.
btw... where's the guide for vb.net and sql2005? :)
dushan
"William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
news:uvzPn1ujEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Perhaps they're trying to discourage piracy?
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
> "Dushan Bilbija" <dbilbija@.msn.com> wrote in message
> news:%235YheoujEHA.2908@.tk2msftngp13.phx.gbl...
> > hello
> >
> > installing sp1 requires the original cd? please tell me i don't... i
> > really
> > don't want to shuffle tons of files around so i can extract a 600mb file
> > just to install a service pack.
> >
> > man, i hate this about msft... i'm always digging up original cds for
> > updates. what a pain. what's the reason for this?
> >
> > dushan bilbija
> >
> >
>|||hi daniel
i'll email you the log file... maybe it'll help debug the install.
in the meantime, i've found a workaround... i dnloaded the eval install, and
used that instead. the original cd is on our corporate network, and i didn't
have access to it.
thanks for the quick reply!
dushan
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:%23wLRh8ujEHA.2500@.TK2MSFTNGP09.phx.gbl...
> We did our best to ensure you would not need the original media when
> installing SP1. Windows installer has some quirks about it that can make
> this a difficult goal to achieve. I have seen this issue happen when
config
> files are copied over top of existing config files. The modified dates
and
> create dates confuse the installer and make it think it needs to recreate
> the file.
> If you send me the setup log file I can see if this is an issue we can
> resolve for our next service pack.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> news:uvzPn1ujEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Perhaps they're trying to discourage piracy?
> >
> > --
> > ____________________________________
> > William (Bill) Vaughn
> > Author, Mentor, Consultant
> > Microsoft MVP
> > www.betav.com
> > Please reply only to the newsgroup so that others can benefit.
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > __________________________________
> >
> > "Dushan Bilbija" <dbilbija@.msn.com> wrote in message
> > news:%235YheoujEHA.2908@.tk2msftngp13.phx.gbl...
> > > hello
> > >
> > > installing sp1 requires the original cd? please tell me i don't... i
> > > really
> > > don't want to shuffle tons of files around so i can extract a 600mb
file
> > > just to install a service pack.
> > >
> > > man, i hate this about msft... i'm always digging up original cds for
> > > updates. what a pain. what's the reason for this?
> > >
> > > dushan bilbija
> > >
> > >
> >
> >
>|||oops... spoke too soon.
the install i have is enterprise edition... so i still need the cd.
dushan
"Dushan Bilbija" <dbilbija@.msn.com> wrote in message
news:Os9KZGvjEHA.3392@.TK2MSFTNGP15.phx.gbl...
> hi daniel
> i'll email you the log file... maybe it'll help debug the install.
> in the meantime, i've found a workaround... i dnloaded the eval install,
and
> used that instead. the original cd is on our corporate network, and i
didn't
> have access to it.
> thanks for the quick reply!
> dushan
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:%23wLRh8ujEHA.2500@.TK2MSFTNGP09.phx.gbl...
> > We did our best to ensure you would not need the original media when
> > installing SP1. Windows installer has some quirks about it that can
make
> > this a difficult goal to achieve. I have seen this issue happen when
> config
> > files are copied over top of existing config files. The modified dates
> and
> > create dates confuse the installer and make it think it needs to
recreate
> > the file.
> >
> > If you send me the setup log file I can see if this is an issue we can
> > resolve for our next service pack.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "William (Bill) Vaughn" <billvaRemoveThis@.nwlink.com> wrote in message
> > news:uvzPn1ujEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > > Perhaps they're trying to discourage piracy?
> > >
> > > --
> > > ____________________________________
> > > William (Bill) Vaughn
> > > Author, Mentor, Consultant
> > > Microsoft MVP
> > > www.betav.com
> > > Please reply only to the newsgroup so that others can benefit.
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > __________________________________
> > >
> > > "Dushan Bilbija" <dbilbija@.msn.com> wrote in message
> > > news:%235YheoujEHA.2908@.tk2msftngp13.phx.gbl...
> > > > hello
> > > >
> > > > installing sp1 requires the original cd? please tell me i don't... i
> > > > really
> > > > don't want to shuffle tons of files around so i can extract a 600mb
> file
> > > > just to install a service pack.
> > > >
> > > > man, i hate this about msft... i'm always digging up original cds
for
> > > > updates. what a pain. what's the reason for this?
> > > >
> > > > dushan bilbija
> > > >
> > > >
> > >
> > >
> >
> >
>

Need Line Breaks in Fixed Width File

Hello,

I have a series of fixed width files, all with the same schema. I need to import the data into a SQL Server table. Each record in the flat file begins with 'D1'. The length of each record (string) is 380. There are cases where the record ends after position 193, and a new record appears in the current string beginning at position 194. So at position 194 'D' appears, and '1' appears at position 195.

In the flat file, I need to insert a line break after position 193 if position 194 = 'D' and if position 195 = '1'. I'm guessing I would do this with a Script Component Transformation. Once the file is edited, then I can bring the data into the table.

What might the script look like? If you have any suggestions, samples, or know of examples on the web you can point me to, please share.

Thank you for your help!

cdun2

Do you really need to insert CR/LF? For what reason?

If all you want is to read this file, use a "Data Task" and use the Flat File connection with a format type of "Fixed Width". This will allow you to define a file import for the file without cr/lf.|||

Thanks for your response. There are cases in the record strings where a new record begins after character 193 instead of at character 1. I need to insert the line break after character 193 so that the records that start at character 194 will correctly start at character 1. Does that make sense?

I suppose I could do a conditional split of some kind, or find some way to separate out the records that start after 193.

I hope that clarifies things.

cdun2

Need Line Breaks in Fixed Width File

Hello,

I have a series of fixed width files, all with the same schema. I need to import the data into a SQL Server table. Each record in the flat file begins with 'D1'. The length of each record (string) is 380. There are cases where the record ends after position 193, and a new record appears in the current string beginning at position 194. So at position 194 'D' appears, and '1' appears at position 195.

In the flat file, I need to insert a line break after position 193 if position 194 = 'D' and if position 195 = '1'. I'm guessing I would do this with a Script Component Transformation. Once the file is edited, then I can bring the data into the table.

What might the script look like? If you have any suggestions, samples, or know of examples on the web you can point me to, please share.

Thank you for your help!

cdun2

Do you really need to insert CR/LF? For what reason?

If all you want is to read this file, use a "Data Task" and use the Flat File connection with a format type of "Fixed Width". This will allow you to define a file import for the file without cr/lf.|||

Thanks for your response. There are cases in the record strings where a new record begins after character 193 instead of at character 1. I need to insert the line break after character 193 so that the records that start at character 194 will correctly start at character 1. Does that make sense?

I suppose I could do a conditional split of some kind, or find some way to separate out the records that start after 193.

I hope that clarifies things.

cdun2

Need help! Used Space in dB and Tran files

I have a database about 39GB in size and is running out of allocated space. In the Enterprise Manager the properties for this dB, show that there is 0.00MB space available. I need to increase the size of the dB.

What I need to find out is, which file group is full so I can expand the appropriate file group instead of expanding all the filegroups. Also, is there a way to find the space usage of each file in the file group and also the Transaction log?

db Size 39GB
14 File Groups
17 database files (mdf & ndf)
2 Transaction Log files
180 Tables

I am aware of the sp_spaceused, but the information it furnishes is not enough for me to make a decision.

Need help urgently!!!!check this query out and see if this will be of any help:

select [Allocated_Size_MB]=size*8/1024, [Max_Size_MB]=maxsize*8/1024, name from sysfiles|||Thanks for your reply. The results from the query give me the allocated size.
How can I find the "used" space (or free/available space) in this allocated space?