Active TopicsMemberlistSearchHelpRegisterLogin

Data Connectivity
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Data Connectivity
Message Icon Topic: Import from text file Post Reply Post New Topic
Author Message
Gurbs
Senior Member
Senior Member
Avatar

Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
Quote Gurbs Replybullet Topic: Import from text file
    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
IP IP Logged
Gurbs
Senior Member
Senior Member
Avatar

Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
Quote Gurbs Replybullet 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
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Gurbs
Senior Member
Senior Member
Avatar

Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
Quote Gurbs Replybullet 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.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.