Cobol Data File To SQL Table Conversion

I am a novice in data base designing. I have MS certification in both design, implementation and administration. Can anyone help me provide information regarding converting from Cobol flat files to sql server tables ?
I will be very grateful.. This is my first assignment.. Got to prove myself... help....

View Replies


Cobol Conversion

This nifty function convert COBOL value to equivalent integer.

If last characer is one of these alphanumeric charactersJ -1
K -2
L -3
M -4
N -5
O -6
P -7
Q -8
R -9
} -0it means that complete value is negative and last character is the equivalent numeric in table above.CREATE FUNCTION [dbo].[fnCobol2Int]
@Item VARCHAR(18)
WHEN @Item LIKE '%[^}jklmnopqr0123456789JKLMNOPQR]%' THEN NULL
WHEN LEFT(@Item, LEN(@Item) - 1) LIKE '%[}jklmnopqrJKLMNOPQR]%' THEN NULL
WHEN RIGHT(@Item, 1) LIKE '[}jklmnopqrJKLMNOPQR]' THEN '-'
END + LEFT(@Item, LEN(@Item) - 1) + CHAR(47 + CHARINDEX(RIGHT(@Item, 1), '}JKLMNOPQR0123456789}jklmnopqr') % 10) AS BIGINT)
E 12°55'05.25"
N 56°04'39.16"

View Replies View Related

What Is The Best Practice To Handle Cobol Redeines In Flat File Data.

Here is my idea but I am looking for the best practice.

Each record can have 3 possibilites.

I would read and write the data 3 times to different tables and add an identity key on all 3 files then I would reassembe the data back together on the identity key and map the data to a fourth and final table.

Any other ideas?



View Replies View Related

Sql Server Data To Cobol Format

I have a need to convert sql server data to cobol format. Has anyone done this?

View Replies View Related

SSIS Flat File Loading - Data Type Conversion

It seems I can do the type conversion in two place:

1. The advanced editor for flat file source => Input and output roperties. And I can set the output columns data type properties there.

2. Using a Data conversion box in Data flow transformation.

Any difference between these two? Which one should be used?

View Replies View Related

SSIS:Data Conversion Failed On Flat File Destination

[Flat File Destination [46500]] Error: Data conversion failed. The data conversion for column "Column 0" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page." What does this error mean exactly?

I am taking columns from a flat file source. Then I am adding some new columns then rewriting the file to a ragged file format with fixed column values. I've taken the Destination component off and it works fine. So I know it could be the destination component but what could it be? Any ideas?

View Replies View Related

Csv File Import: Bulk Insert Data Conversion Error (type Mismatch)


Iam trying to import data from a csv file into my table in SQL Server 2000. My table is called as temp_table and consists of 3 fields.

column datatype
-------- -----------
program nvarchar(20)
description nvarchar(50)
pId int

pId has been set to primary key with auto_increment.

My csv file has 2 columns of data and it looks like follows:

program, description
"prog1", "this is program1"
"prog2", "this is program2"
"prog3", "this is program3"

Now i use BULK INSERT like this


to import data into my table in SQL server and it gives me this error

"Bulk insert data conversion error (type mismatch) for row 2, column 3 (pId)"

I guess i have to use fileformat or something since i dont have anything for pId field in the csv file to make it work...

Please help me out guys and please post a snippet of code if you have.

Thank You.

View Replies View Related

Data Conversion Errors On Excel Import Into Existing Table

Recently installed Sql Server 2005 client and am now attempting to import data from a spreadsheet into an existing table. This works fine with Sql Server 2000 but I am getting data conversion truncation errors that stop the process when this runs using import utility in Sql Server 2005.

Any help would be appreciated.

View Replies View Related

Question About Automating Data Conversion And Updates To A Table In SQL Database


I will be getting data in either Excel or Access form on a daily basis. I would like to automate the process of converting this (excel or access) data to a table in an existing SQL database. Since this conversion needs to performed on a daily basis, note that I need to update the table that contains data from the day before.

Is it possible to do this and if it is possible, can someone tell me how to do it.

Thanks in advance,

Joe green

View Replies View Related

Cobol -&> SQL2005

I'm not "cobol person" but now, I have to bind a cobol application (from mainframe) to query SQL 2005

Have somebody had this task?

View Replies View Related

Upload Database From Sql 7 To COBOL???

I developed a database in MS SQL server7. But the main database was developed in COBOL. Is there anyway I can upload my
database to mainfram? Thank you in advance.

View Replies View Related

Create Table From Text File, Extract Data, Create New Table From Extracted Data....

Hello all,

Please help....

I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....

I need to extract data from DataFile table, here's my query:

select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';

Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.

Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?

Any help would be highly appreciated.

Thanks in advance.

View Replies View Related

Exported Flat File Data Will Not Import To Same Table Without Extensive Data-type Manipulation

I'm moving data between identical tables and have to use a flat file as an intermediary. I thought: "No problem, SSIS can do a quick export to a file, then move the file to another server, then use SSIS to import the data to the new server."

Seems simple, right?

I'm hitting all sorts of surprising data conversion errors. I used the export wizard to create the export package. This works fine. However using the same flat file definition, the import package fails -- even when I have no destination. That is I have just one data flow task that contains only one control: the Flat File source. When I run the package the flat file definition fails with data type conversion and truncation errors. One of the obvious errors is for boolean types. The SQL field is a bit, SSIS defined the column as DT_BOOL, the output of the data are literal text values "TRUE" and "FALSE". So SSIS converts a sql datatype of bit to "TRUE" and "FALSE" on export, but can't make the reverse conversion on import?

Does anyone else find this surprising? I would expect that what SSIS exports, it can import given all the same table and flat file definitions. Is SSIS the wrong tool to do such simple bulk copies? I'd like to avoid using BCP because this process will need to run automatically within SQL Agent so we can leverage all the error tracking and system monitoring.

View Replies View Related

How To Convert Cobol ZONED Decimal Using SSIS


I am new to this forum and SSIS also.

I searched but could not find any answer here so I am posting my question.

We get some cobol text file that has Zoned Decimal also.

We want to use SSIS to convert the file in to SQL Server Database but we want to avoid using 3rd party s/w..

In DTS it was not possible..

Is it possible to convert Zoned Decimal to Decimal in SQL Server.

Thanks in advance.


View Replies View Related

VSAM Cobol Files (Array Format): Connector For SSIS

Hi ,

Just wondering what is the way to connect to Cobol VSAM files (data in array format) using SSIS ?

Where can I get the OLEDB/ODBC drivers for the same?



View Replies View Related

Data Conversion Failed. The Data Conversion For Column &&"value&&" Returned Status Value 4 And Status Text &&"Text Was Truncated Or On

Hi Experts,

I am extracting data from SQL Server 2005 to flat file destination. I am using SQL Command to specify the data selection query. One of my query uses Replicate function to derive a column value. When I execute this package it fails with the error "Data conversion failed. The data conversion for column "value" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page".

The reason for the problem is that, it is taking the InputColumnWidth of the flat file destination as 8000 and I specified the OutputColumnWidth as 4.

If I change the OutputColumnWidth to 8000, it is working without any error but resulting in the column width of 8000.

I tried using DerivedColumn Transformation's Type cast and DataConversion Transformation but still I am getting the same error in the respective Transformation components.

Can anyone suggest how to solve this issue.

View Replies View Related

Table Data File

How to make the diffrent data files for different table in a database (SQL SERVER 2K)? By default it is the single data file and log file for the data base. But I wnat the separate data files for all my table in the database. So, any one can help me out in this regard......................???

View Replies View Related

Create CSV File From A Data Table

 Hi,     I am working on SQL Server 2005 Express and what i want to do is to created a CSV file from a database Table or a View......I search around and find that I Can use BCP for that purpose..Ne other good idea if you people BCP is not working on my PC while i am connected to database server...So plz suggest me the right way to do so...  thanx 

View Replies View Related

Table Data Export To .sql File.

Rookie qustion here quys,
Using SQL Sever 2005 Express and Visual Web Devoloper Express.
I'm trying to get data from my local SQL sever database to my shared hosting database. SImple enough but...
1.) GoDaddy does not allow remote connections to the DB server
2.) With GoDaddy you can't just upload an .MDF file to your remote directory and script a connection string. You have to reference the DB server. Can't upload DB file to DB server.
As a work around, I've seen several .sql files that have all the T-SQL that will create a database and tables then insert the data. I've google'd extensively and searched several forums with no luck as to how to export my local data to file. I could then copy and paste into an online utility GoDaddys has to insert the data.
Is there a relatively easy way to export table data to a .sql file??? When I try using SQL Server Management Studio Express and "Scipt Table As> Insert To> File", It just gives me something like the following...
INSERT INTO [pubs].[dbo].[authors]           ([au_id]           ,[au_lname]           ,[au_fname]           ,[phone]           ,[address]           ,[city]           ,[state]           ,[zip]           ,[contract])     VALUES           (<au_id, id,>           ,<au_lname, varchar(40),>           ,<au_fname, varchar(20),>           ,<phone, char(12),>           ,<address, varchar(40),>           ,<city, varchar(20),>           ,<state, char(2),>           ,<zip, char(5),>           ,<contract, bit,>)
Plenty of data in this table, no data in script   ???
Can SQL Server Management Studio Express do what I wanting to do? If not, any suggestions? Know of a better hosing company?

View Replies View Related

Table Data Export Into An XML File


I need to export data from some tables into an XML file format.

Any help please?

John Jayaseelan

View Replies View Related

Importing Csv And XML Data From 1 File Into A Table

Hello - I am looking for a way to import data into a table where 1 of the fields holds an XML file but the rest of the fields hold basic csv data. We want to be able to set up a simple import script in our program so that a user can upload 1 file that contains both the data for the normal fields (example: last name, first name, date, source, etc) and the XML string that is parsed later on in our program. The entire XML string resides in its own field. I am wondering if there is anyway all of the data could be combined in 1 file. Thanks in advance.

View Replies View Related

Help For Importing CSV Data File To Table

Hi all-

I am in need of some help importing a .CSV file into a SQL Server 2005 Enterprise Edition.
The problem is I already implemented Bulk Insert task in SSIS but it is not importing any data. My detailed layout is as follows :
In SSIS package1 -
In Control Flow Bulk Insert Task has been inserted
Properties of Bulk Insert Task:
Connection adtc009d.ganny
Destination Table ganny.dbo.t4
Format Specify
Row Delimiter {CR}
Column Delimiter Comma{,}
Source Connection
File r.csv
Options Check Constraints
Maxerrors 20

This bulk insert task is connected to Data flow task, if we click edit to data flow task, data flow section will come, here Flat file source & OLE DB Destination is there. Flat file source is connected to OLE DB Destination.

Properties of Flat File
Connection Manager
Flat file connection Manager
here by clicking new link flat file properties to this.
by clicking preview all data are visible
Properties of OLE DB Destination editor
Oledb connection manager adtc009d.ganny
Data access mode: Table or View - fast load
Name of Table or view dbo.t4

After designing all this then if I start debugging I could able to get records are imported to a table.
Please suggest me where I am going wrong.

Thanks in advance

View Replies View Related

File Conversion - DTS

Is there a way to convert an excel file as an tab delimited text file before start importing using DTS or otherwise ?

Could some one know what steps involved here ?

Thanks for your help.


View Replies View Related

File Conversion

Hello I have a cvs that i manual convert to a xls file. I have created a SSIS package. I was wondering is there a script or tool that will automatic convert the cvs file to a xls file when i run the package. Other wise i will have to convert all the files manual to xls.

View Replies View Related

Sql Conversion From Bak File

i have a BAK file that was created from M S S Q L 7. how can i open or convert that file to a different format?

what i'm trying to do is: take an existing database with its contents and then save it in a different file format to be used in a different database.

i'm trying to use Case Studio

any help?

View Replies View Related

Table Data In Which Data File

Dear All,

I have SQL Server 2000.

I have one database which have 3 data files datafile1.MDF, datafile2.ndf, datafile3.ndf.

Now if i want to get list of tables in each datafile how can i get it ?

Thanks & Regards,

View Replies View Related

Move Data From Excel File To A Table (MS SQL)

I have an application , user will read information in Excel file and insert that data into my application, I think it spend a lot of time. I want to make a tool which move data from Excel file to a table in My application (MS SQL) automaticly. How to do it, anybody has tool or know how to do, pls help me.thanks.

View Replies View Related

Access File/Get Data Can't Attach To A SQL Table.

In Access 97, I am trying to attach to a table in my SQL Server 2000db.I use File/Get Data, which displays all the SQL tables including theone I can't attach to. (I can attach to all the other SQL tables.)But, when I select that table I get a message that tells me the nameis invalid. I don't understand this because it gives me the name inthe Get Data dialog, and I don't have to enter it manually.Any ideas?Thanks,Bob C.

View Replies View Related

Export Data From Table To Text File

I need to export data from a table to a text file, where the data in the table is deleted after written to the file. It is simple using DTS, but I want to do the export in "chunks" of data, committing the delete say after every 1000 rows.

My thought was a stored procedure would be easy enough to do this (done these in Oracle many times), but I don't know the quickest way to export a row of data from a stored procedure to a text file. Isn't using a command-line shell too slow? What are my options?

View Replies View Related

Data Formate When DTS Table To Excel File

I tried to dts a table to an excel file. The data types of all the columns in the table are varchar. I also want the data in the destination excel file to be text, so I didn't change the data format in the dts package. But after ran the dts, every value of every cell of the excel file has a single quote before it (not in the cell itself, but in the value shown space right below the toolbar). I tried to dts the table into a excel template which I predefined all cells to be text type. But some of the columns do not have the single quote anymore, while others still keep them. Since the other system that going to process the excel file does not expect the single quote, the process always fails.

Would anybody tell me how to get rid of the single quote?
Thanks a lot.


View Replies View Related

Insert Data From A Text File Into The Table

Hi. my websever mysql manager allows to Insert data from a text file into the table.
currently i have a table with the following fields.

email name country

so i wanto insers data from a text file into this table.
so my question is : how the text file format should be prepared.

i.e. the first record would be > john us

View Replies View Related

Pulling Data From Flat File To A Table

I need to do some advanced formatting in the text file, because the data in the text file is bit complex. I have some knowledge of using BCP, BULK INSERT and bit about FORMAT FILES but I want to do some advanced formatting(using IF condition and all) to pull the data from text file.
Can anyone please tell me how can I perform bit advanced formatting using BULK INSERT?


View Replies View Related

Copyrights 2005-15, All rights reserved