Anyone have some code to import bank files to SQL Server 2000 tables in the formats BAI or BAI2? I'm using a DTS package currently, but I'd like to have more control over this by using T-SQL or vbscript or something.
These files have complex structures/multiple record types. Not to mention my main problem of trying to use a bulk insert which doesn't seem to work because of something to do with the row delimiters. Not too sure what's up with that because I usually do this for all my flat file imports. I'm certain vbscript will work, but I'd like to know if anyone out there has already built something for these particular files??
Thanks!
Anyone have some code to import bank files to SQL Server 2000 tables in the formats BAI or BAI2? I'm using a DTS package currently, but I'd like to have more control over this by using T-SQL or vbscript or something. Thanks!
Brief overview...Running SQL Server 2003 Server Enterprise 64 bit - All Service Packs and patches current SQL Server 2005 Enterprise Edition 64 bit Build Microsoft SQL Server 2005 - 9.00.3054.00 (X64) Mar 23 2007 18:41:50 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I cannot import any SSIS packages nor crete any new folders under stored packages. I hve googled the news groups and looked at BOL to no avail. HELP!!!!
Hi, I am using SQL Server 2000 as part of SBS 2003. I have an odd problem with a table in my database.
On Monday, due to a problem we had on Friday afternoon, I was forced to restore the database to 12:30pm Friday. Since then, everything has been fine, except that in one table, a bank of 15 records cannot be edited. There are 25,000+ records in the table, and all of the rest are fine and can be edited. If I try to edit one of the "bad" records, SQL times out or hangs indefinately. This is true if I edit the table direct, if I use SQL Analyser or if I use my FE application. There is no other error message. There are no triggers on the table.
As far as I can see, the id's of all of the bad records are the same as those which would have been entered on Friday afternoon, but which were lost with the restore. Records before and after the bank of 15 can be edited ok.
Last night, with no other users on the system, I exported the table, deleted the original, created a new table with the same name and fields as the original and used Analyser to populate the new table from the export. I then tried to edit the bad records and it worked fine. However, this morning, with other users on, it fails again and the records cannot be edited.
I have used DBCC CHECKDB and it displays no errors.
Can anybody suggest what this might be and how I can get round it?
I have replaced the double-pipes with commas to enable a simple copy/paste when I use the "INSERT INTO" commands.
However, when I use this method, I would have to insert a single quote before and after each state, city, and county to get the row inserted without error. Obviously I'm not going to do that for 7,000 rows of data...and input 21,000 single quotes.
When I try importing the file using phpMyAdmin, it takes absolutely forever...to the point where I cancel every time because I'm pretty sure nothing is happening.
Is there any resolve to this? How do I get this data into my ZIPCODES Table? I've spent hours trying to manipulate my commands, the data, and anything else I can think of. I'm sure there is a solution, I'm just limited in my knowledge to figure it out.
Hope all is well and thanks in advance for any help or response!
Im new so apologies in advance if this is an easy one.
I have a single table which holds: ID (PK) EnteredDate MonetaryValue REF_ValueType
It's basically used to record income and expenditure and the records look something like:
ID EnteredDate MonetaryValue REF_ValueType 1 21/01/07 12.34 Received 2 22/01/07 -8.34 Paid 3 23/02/07 100.28 Received
What Im trying to do is to write sql that will return all the records in the form of a bank statement with received values in one column and paid values in another e.g. Date Income Expenditure 21/01/07 12.34 22/01/07 -8.34 23/02/07 100.28
Ive tried all ways but I still can't get it to work. Is there a simple way.
All advice most definitely appreciated, it's sending me crazy. Many thanks in advance
Hi i have a question. How can i import dump from a text file , to an exsiting database, i´m will not useing DTS, i what to use BULK cmd in sql queryanalyser etc.
I have SQL 2000 running under windows 2000 server.
Question: I'm receving CSV files from the field. I would like to built SQL database from daily CSV files. I've been doing this manually :( however, is there anyway I can do this task automactically so that once new daily CSV file ~200KB appear in the folder, progarm automatically insert recent file to SQL database...:) Do I need to write VB progarm for this? Please help...
I have created a stored procedure to handle xml data. One of the inputs to this procedure is xmldata which is basically the contents of the xml in a file. Now I would like to create a SSIS package that takes the xml files from the network and uses this SP. Should I have a file system task inside a for loop container? How do I get started on this please? Thanks
I have created a stored procedure to handle xml data. One of the inputs to this procedure is xmldata which is basically the contents of the xml in a file. Now I would like to create a SSIS package that takes the xml files from the network and uses this SP. Should I have a file system task inside a for loop container? How do I get started on this please? Thanks
Dont know whether this is of any use to anyone or it has been done before but there are a lot of posts on here regarding date calculation issues & usually the most straight forward answer is to compare against a table of dates.
So while looking at Bretts blog and another post on here, i thought i'd post this on here http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698
Special thanks to rockmoose & BOL (as always)
Edit: It also moves bank holidays to the following Monday (and Tuesday - xmas) if the bank holiday(s) falls on the weekend
SET DATEFIRST 1 SET NOCOUNT ON GO
--Create ISO week Function (thanks BOL) CREATE FUNCTION ISOweek (@DATE datetime) RETURNS int AS BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') --Special cases: Jan 1-3 may belong to the previous year IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 --Special case: Dec 29-31 may belong to the next year IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END GO --END ISOweek
--CREATE Easter algorithm function --Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689) CREATE FUNCTION fnDLA_GetEasterdate(@year INT) RETURNS CHAR (8) AS BEGIN -- Easter date algorithm of Delambre DECLARE @A INT,@B INT,@C INT,@D INT,@E INT,@F INT,@G INT, @H INT,@I INT,@K INT,@L INT,@M INT,@O INT,@R INT
SET @A = @YEAR%19 SET @B = @YEAR / 100 SET @C = @YEAR%100 SET @D = @B / 4 SET @E = @B%4 SET @F = (@B + 8) / 25 SET @G = (@B - @F + 1) / 3 SET @H = ( 19 * @A + @B - @D - @G + 15)%30 SET @I = @C / 4 SET @K = @C%4 SET @L = (32 + 2 * @E + 2 * @I - @H - @K)%7 SET @M = (@A + 11 * @H + 22 * @L) / 451 SET @O = 22 + @H + @L - 7 * @M
IF @O > 31 BEGIN SET @R = @O - 31 + 400 + @YEAR * 10000 END ELSE BEGIN SET @R = @O + 300 + @YEAR * 10000 END
RETURN @R END GO --END fnDLA_GetEasterdate
--Create the table CREATE TABLE MyDateTable ( FullDate datetime NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED, Period int, ISOWeek int, WorkingDay varchar(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y' ) GO --End table create
--Populate table with required dates DECLARE @DateFrom datetime, @DateTo datetime, @Period int SET @DateFrom = CONVERT(datetime,'20000101') --yyyymmdd (1st Jan 2000) amend as required SET @DateTo = CONVERT(datetime,'20991231') --yyyymmdd (31st Dec 2099) amend as required WHILE @DateFrom <= @DateTo BEGIN SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6)) INSERT MyDateTable(FullDate, Period, ISOWeek) SELECT @DateFrom, @Period, dbo.ISOweek(@DateFrom) SET @DateFrom = DATEADD(dd,+1,@DateFrom) END GO --End population
/* Start of WorkingDays UPDATE */ UPDATE MyDateTable SET WorkingDay = 'B' --B = Bank Holiday --------------------------------EASTER--------------------------------------------- WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Good Friday OR FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Easter Monday GO
UPDATE MyDateTable SET WorkingDay = 'B' --------------------------------NEW YEAR------------------------------------------- WHERE FullDate IN (SELECT MIN(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dw,FullDate) NOT IN (6,7) GROUP BY DATEPART(yy,FullDate)) ---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------ OR FullDate IN (SELECT MIN(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1 GROUP BY DATEPART(yy,FullDate)) OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1 GROUP BY DATEPART(yy,FullDate)) --------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------ OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1 GROUP BY DATEPART(yy,FullDate)) --------------------XMAS(Move to next working day if on Sat/Sun)-------------------- OR FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN DATEADD(dd,+2,FullDate) ELSE FullDate END FROM MyDateTable WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26)) GO
---------------------------------------WEEKENDS-------------------------------------- UPDATE MyDateTable SET WorkingDay = 'N' WHERE DATEPART(dw,FullDate) IN (6,7) GO /* End of WorkingDays UPDATE */
--SELECT * FROM MyDateTable ORDER BY 1 DROP FUNCTION fnDLA_GetEasterdate DROP FUNCTION ISOweek --DROP TABLE MyDateTable
1) CustomerID 2) FirstName 3) MiddleName 4) SurName 5) Title 6) Marital Status 7) Education 8) Occupation 9) Annual Income 10) Line of Business 11) DOB 12) Father Name 13) Mother Name 14) SpouseName 15) Gender 16) Email 17) MainTel 18) Home Tel 19) Passport Number 20)---------------------- 21)- - - - - - - - - - -
100)------------------- Above mentioned list is a snapshot of our customer master table ,which contain approximately 100 attributes related to a customer.
We are designing an application for banking sector (but NOT Core banking solution),for which we may need to capture variable number of addresses for bank's customer,i.e more then three types of addresses Fixed,Temporary and Communication addresses(which is generally the case with all banks). A single address includes address1/address2/city/country/state/pincode fields. In context of OLTP database,We have option to put multiple addresses in child table but that involves various joins at the time of data retrival and slow down the query.
As another option we can can create redundent addresses columns(address1/address2/city/country/state/pincode) in master table that will accumulate addresses if demand for more then three type addresses arises(although there is reasonable numer of extra addresses is expected, i.e 10)
Database is expected to serve the records of 25 million(approx) bank's customer,so does someone can suggest me how to maintan the balance between two approches.
Hi All, can some body help me out ????? Requirement: i want to export a complete database into an excel file... i can do this from wizard available in sql server 2000/2005 (DTS Export/Import wizard). but i need to do it through an application (windows or web).... and i also want to import the same excel file on single button click... can some body help me out
Hi, I have about 300-400 XML files I want to load in my SQL database (2005). The following code will load one (1) file. How do i do a mulitple collections? INSERT INTO MEL (DATA) SELECT * FROM OPENROWSET (BULK'C:TempCHAPTER1.xml', SINGLE_BLOB) AS TEMP Thanks,
I'm new to SQL server. I would like to ask how to do the following. Now I have many CSV files in a directory, which are uploaded through ftp from my client computers daily. How can I automatically import those CSV files to the SQL server? Can I do it by a script or a stored procedure or something else?
I have data in excel files I want to import it to existing SQL Server database. I can use SQL Server built in import tool but before Importing, the data needs to be validated. What is the best way to do this?
I have 8GB of text files which are basically log files from the past few years. There is 24 text files per directory which are labeled for every day (so they are not all in 1 folder). It would make reading them much easier if I could import them to SQL but I only seem to be able to import 1 at a time? (with the wizards :eek: )
Surely there is a way to mass import without all the costly applications that google searches give me? cheers :P
I have a job that calls about 5 DTS's. Each DTS imports a text file. The problem is that some of the files may not be there every day. How do I exit the DTS without an error if the file does not exist, but I want my job to keep running...
I have a script which imports the contents of a csv file from our CRM system and updates a table in my database. This works OK but the problems I have are that a) sometimes there is more than one file in the folder, and b) that I wish to move any csv files that have been imported into an archive folder. The csv files arrive with a time/datestamp and I currently rename them manually to FREXPORT before importing (the name is in the format FREXPORT_20141101_1217.csv).How do I:
1) get it to process the file without me having to manually rename the file(s) each time, 2) if there is more than 1 file in the folder process all the files and 3)move the correctly processed files to an archive folder which is: importarchive?
Ultimately, I would like the script to be run as a scheduled job, so it also has to deal with the fact that sometimes there will be no files to import too.
I have several hundred .csv files that have a specific cell that I need to get into a SQL table. These files are named after the date on which they were created...ie 8252005 would be todays date. Im looking for a way to import this cell to SQL... the same cell in each file.... Thanks for any help
I have a task to import *.log files in to the Microsoft SQL Server and need help in this regards.
This import should be the best table structure/format for the web log (considerations for import time, size, indexing, querying, reporting, etc.), and best utilize all available space on the server
I'd like to set up a dts package to import .csv files and on testing one .csv with only one row I am getting this error:
Error Source: Microsoft Data Transformation Services Flat File Rowset Provider
Error Description: Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
data set from .csv: -31,0,"CENTER","","","64 ROCK DR. #4100","BETH","MD.","20417","0470-5",30109,10598,"52492000 083","1018","406800","6896CE","7007","G00887",1,13983.24,2139.66,42741.9,150101.36,830317.41,1562843.99,30128.45,2,506,0,0,0,0,0,0,37.87,1,0,0,476.39,472.15,0,1191.12,0,90073.46,43038.97,0,309926.59,-14880.49,20060102,20090101,"A","","",""," ","C62454",0,0,0,0,0,0,0,0,8.39,0,0,0,0,"",0,"00000000",""
i'm trying to find out if SSIS is a right toolbox for importing of many SPSS-files into the large market research data warehouse.
A simplified version of SPSS-datasheet looks like this:
- many columns (up to 3000). some of them are interview attributes (InterviewID, Date, etc.) and variables, representing survey questions
- relative few rows with "cases" or "interviews"
- in cells there are answers to the questions (in columns) by respondent (in rows)
As first step i would like to unpivot the dataset in the following way:
- Pass-Through: all interview attributes, that shouldn't be unpivoted
- Input Columns: rest 2990 columns
- Destination Column: Question
- Pivot Key Value Column Name: Answer
It works allright, except that i has to manually define destination column for all 2990 input columns, which takes a lot of time (multiplied by the number of SPSS-files i want to import). Is there a way to automate this (default value for destination column and/or scripting?)
Does anyone have a completed script that will import Perfmon logs from csv format into an existing database that follows the SQL Log File Schema EG; CounterData, CounterDetails, DisplayToID tables accurately. I don't know enough T-SQL to get it right. Thanks for any input you may have.
Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables . I have to create a tool to automate the porting using C# .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .
