Print Page | Close Window

Import from text file

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19189
Printed Date: 03 May 2024 at 3:13am


Topic: Import from text file
Posted By: Gurbs
Subject: Import from text file
Date Posted: 27 Feb 2013 at 10:13pm
Hi all,

I have a question. From a customer of ours, we receive a data file on a daily basis that we need to upload into our system. Only problem is, that they deliver this file in a .txt format. I managed to set this as my database source (new connection --> Access/Excel --> database type text). However, when I go into the table in Crystal Reports, he split it the field into 3 different fields. 1 with the same name as the table (this makes sense), but also one named F2 and one named F3. This wouldn't even be the biggest problem, but when I combine the 3 fields, I seem to be missing information at the end in some cases. Also, when I check the amount of characters in the fields (this should always be the same amount), they don't always match up. Does anyone know what I am doing wrong, or how I could fix this?

Thanks in advance.

Kind regards,

Gerben


Replies:
Posted By: Gurbs
Date Posted: 27 Feb 2013 at 11:32pm
Small update: I figured out why it is splitting up in 3 different fields. It does this when there is a comma in the line. Is there a way I can say not to split op the field if that's the case?

I still have the issue with missing text though

Posted By: hilfy
Date Posted: 01 Mar 2013 at 3:32am
So what are you trying to use Crystal for?  You can't really use it to upload the file into the database.  It would be better to report on the data after it is uploaded instead of trying to use the text file as a data source.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Gurbs
Date Posted: 01 Mar 2013 at 3:42am
I'll try to explain what I am using Crystal for.
Currently, we have people checking when we receive the text file. Then they manually transform this into an excel document. This excel document is then upload into a program then put's it on the server, from where it will be loaded into our database. But recently we purchased a Crystal Report scheduler. So now, when we receive a file, the scheduler picks it up, and can run a report. I am trying to use Crystal to change it around, and export it to automatically to the folder from where it will be uploaded into the system.

What I did now is, as soon as the file hits our server, it is being transformed into an excel file. This excel file is linked to an access database, putting all the files into one column. My report is based on that access database, from where I am able to transform it into the correct format (.CSV with different fields). However, this is quite the workaround. I was wondering if it would be possible to just use the text file in Crystal.

Automating this process saves a lot of time, as we don't have to keep an eye on the server, don't have to transform the file, and don't have to upload it anymore.

Posted By: hilfy
Date Posted: 01 Mar 2013 at 4:31am
You could try setting up an ODBC DSN that connects to the file.  In the setup for the DSN you would define how the fields are set up in the file.  Then use the ODBC connection in the report instead of connecting directly to the file.
 
When you set up the DSN you need to remember two things:
 
1.  Crystal will only work with 32-bit connections.  So, if you're running on an 64-bit server, you need to use the 32-bit ODBC manager to create the DSN.
 
2.  This must be a System DSN, not a User DSN.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window