GoAnywhere
  • Contact Us
  • Login
  • Live Chat
  • Free Trial
  • Support
  • Company
Request Demo
GoAnywhere
MENU
  • Solutions
    • Managed File Transfer
    • Cloud File Transfer
    • Secure FTP
    • AS2 Transfers
    • OpenPGP
    • Sharing & Collaboration
    • Agents
  • Industries
    • Banking & Finance
    • Healthcare
    • Higher Education
    • Insurance
    • IT & Telecom
    • Logistics
    • Manufacturing
    • Media & Entertainment
    • Public Sector
    • Retail
  • Platforms
    • Windows
    • VMware
    • Linux
    • Amazon EC2
    • Azure
    • IBM i (iSeries)
    • AIX and UNIX
    • Mac OS
  • Products
    • GoAnywhere MFT
    • GoAnywhere Gateway
    • Free FTP Client
    • Free FTP Server
    • Free Collaboration
    • Free OpenPGP Encryption
  • Resources
    • Brochures
    • Case Studies
    • Compliance
    • Testimonials
    • Video Library
    • Video Reviews
    • Webinars
    • White Paper Library
  • Blog
  • Quick links
    • Unanswered topics
    • Active topics
    • Search
  • FAQ
  • Register
  • Login
  • Board index
  • GoAnywhere Director
  • Knowledge Center
  • Example Projects

Import Fixed-Width file into a Database

Looking for an example project to get you started?
5 posts Page 1 of 1
  • Print view
 Post a reply  

Import Fixed-Width file into a Database

Support_Julie
Support Specialist
 
Posts: 80
Joined: Thu Mar 05, 2009 3:49 pm
Location: Ashland, NE USA
  • Website
by Support_Julie » Tue Jun 16, 2009 10:29 am
Reply with quote
Fixed-Width file to Database

Fixed Width file being imported:
Code: Select all
1111111111aaaaaaaaaaaaaaaaaaaaaaaaa 11111111.11
2222222222bbbbbbbbbbbbbbbbbbbbbbbbb-22222222.22
3333333333ccccccccccccccccccccccccc 33333333.33


Field layout of database table receiving data:
Code: Select all
PARTNBR             Character                   10     
DESCRIPTION         Character                   25       
PRICE               Packed Dec                  10,2


This project will get a file from an FTP server, read the Fixed-Width file and then insert the data into a database table. Please note that on the SQL INSERT statement, you need to enter one question mark for each field you are inserting into. In this example three fields are being inserted into. That insert statement is: INSERT INTO DEMO.TEST3 (PARTNBR, DESCRIPTION, PRICE) VALUES(?, ?, ?)
When inserting into 2 fields you would have two questions marks in the insert statement i.e.: INSERT INTO LIB.FILE VALUES(?,?)

Code: Select all
<project name="Fixed-width to database" mainModule="Main" version="1.0" logLevel="debug">

   <module name="Main">
      <createWorkspace />

      <ftp label="Connect to FTP server" resourceId="Dev 54">
         <get label="Get Fixed-width File" sourceFile="/Demo/TEST1.TXT" destinationDir="${system.job.workspace}" type="binary" destinationFilesVariable="fixedwidthFile" />
      </ftp>

      <readFixedWidth label="Read Fixedwidth File" inputFile="${fixedwidthFile}" outputRowSetVariable="fixedwidthData" skipFirstRow="false" recordDelimiter="CRLF">
         <data>
            <column index="1" name="PARTNBR" type="CHAR" size="10" alignment="left" />
            <column index="2" name="DESCRIPTION" type="CHAR" size="25" alignment="left" />
            <column index="3" name="PRICE" size="12" alignment="right" />
         </data>
      </readFixedWidth>


      <sql label="Connect to DB" resourceId="DEV54">
         <query label="clear out file">
            <statement>DELETE
FROM
   DEMO.TEST3
</statement>
         </query>
         <query label="Import Data" inputRowSetVariable="${fixedwidthData}">
            <statement>INSERT INTO DEMO.TEST3
   (PARTNBR, DESCRIPTION, PRICE)
VALUES
   (?, ?, ?)</statement>
         </query>
      </sql>


      <deleteWorkspace />

   </module>

   <description>FTP Get Fixed-width file and insert into Database</description>
</project>


* When doing an FTP GET of a Fixed width file, change the transfer type to BINARY to maintain the End of Record marker of CRLF.
Julie
Sr. Product Specialist
HelpSystems (MFT office)

Re: Import Fixed-Width file into a Database

cmayil
 
Posts: 7
Joined: Thu Sep 08, 2016 8:43 am
by cmayil » Fri Mar 03, 2017 7:41 am
Reply with quote
Hello Team,
Could you please let me know how to do bulk upload from csv/text file to Database.
I have text files with 22,74,700 records. Executing SQL insert query as mentioned this topic, taking longer than the expected time.
It has been executing for around 2 hours.

Pls help me on this.
Thanks

Re: Import Fixed-Width file into a Database

Support_Rick
Support Specialist
 
Posts: 591
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ
  • Website
by Support_Rick » Fri Mar 03, 2017 4:34 pm
Reply with quote
Please look at the Advanced tab under the SQL/Query task... it will give you the "batch size" option as below:

Batch Size.png
Batch Size.png (8.71 KiB) Viewed 1033 times
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696

Re: Import Fixed-Width file into a Database

cmayil
 
Posts: 7
Joined: Thu Sep 08, 2016 8:43 am
by cmayil » Tue Mar 21, 2017 3:04 am
Reply with quote
Hi Rick,
Thank you for your reply.
It doesnt drastically increase the performance duration.
I had set the batch size value = 1024, I afraid that my server would hung If I set the size more than this.
Without setting batch size , it took 79 minutes to get completed. After setting the batch size - 1024, it took 63 minutes.
Please refer the attached execution summary result. Is there any other way to increase the execution performance?

Thanks
Attachments
BulkUpload by setting Batch Size.JPG
Result summary
BulkUpload by setting Batch Size.JPG (30.9 KiB) Viewed 1005 times

Re: Import Fixed-Width file into a Database

Support_Rick
Support Specialist
 
Posts: 591
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ
  • Website
by Support_Rick » Tue Mar 21, 2017 8:50 am
Reply with quote
You could increment up from the 1024 ... even try 2048 and see what happens.

If you're afraid your server will hang, then you might have to look into why you feel that way about your server. Remember, this is a JDBC URL connection to your server. Making sure those connections have plenty of resources will make this run smoother. We're just giving you an option to increase the slice it sends for each transfer. Instead of 1 for each... sending 2048 for each could significantly decrease your throughput time.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
 Post a reply  
5 posts Page 1 of 1
  • Print view
Return to “Example Projects”
  • GoAnywhere MFT
  •    ↳  Knowledge Center
  •    ↳  Community Forum
  • GoAnywhere Director
  •    ↳  Knowledge Center
  •       ↳  Example Projects
  •    ↳  Community Forum
  • GoAnywhere Services
  •    ↳  Knowledge Center
  •    ↳  Community Forum
  • GoAnywhere OpenPGP Studio

Who is online

Users browsing this forum: No registered users and 0 guests
  • Board index
  • All times are UTC-05:00
cron
 

 


Login


Company

  • About Us
  • Blog
  • Certifications & Partnerships
  • Upcoming Events
  • News
  • Our Customers
  • Testimonials
  • Awards & Recognition

Products

  • GoAnywhere MFT
  • GoAnywhere Gateway

Resources

  • Brochures & Data Sheets
  • Case Studies
  • Compliance
  • Testimonials
  • Videos
  • Video Reviews
  • Webinars
  • White Papers

Compliance

  • PCI
  • HIPAA
  • GDPR
  • FISMA

Industries

  • Banking and Finance
  • Healthcare
  • Higher Education
  • Insurance
  • IT & Telecom
  • Media & Entertainment
  • Logistics
  • Manufacturing
  • Public Sector
  • Retail

Partners

  • Join Our Partner Program
  • Partner Login

Support

  • Overview
  • Contact Us
  • Customer Login
  • Downloads
  • FAQ
  • Live Chat
  • Release Notes
  • Support Forum
  • Register for Training

How to Buy

  • Request a Quote
  • Find a Local Reseller
  • Referral Program

Notices

  • Copyright Notice
  • Privacy Notice
HelpSystems

1-800-949-4696 | goanywhere.sales@helpsystems.com | Privacy Policy
Copyright © 2008 - 2018 HelpSystems. GoAnywhere® is a registered trademark of HelpSystems.

Blog LinkedIn Twitter Facebook YouTube
  • Solutions
    • Managed File Transfer
    • Cloud File Transfer
    • Secure FTP
    • AS2 Transfers
    • OpenPGP
    • Sharing & Collaboration
    • Agents
  • Industries
    • Banking & Finance
    • Healthcare
    • Higher Education
    • Insurance
    • IT & Telecom
    • Logistics
    • Manufacturing
    • Media & Entertainment
    • Public Sector
    • Retail
  • Platforms
    • Windows
    • VMware
    • Linux
    • Amazon EC2
    • Azure
    • IBM i (iSeries)
    • AIX and UNIX
    • Mac OS
  • Products
    • GoAnywhere MFT
      • Administration
      • Connectivity
        • Amazon S3 Bucket
        • AS2
        • Database
        • FTP
        • FTPS
        • GoFast
        • HTTP(S)
        • ICAP
        • Mail Servers
        • Message Queue (MQ)
        • Native Calls
        • Network Shares
        • SCP
        • SFTP
        • SMS
        • SNMP
        • WebDAV
      • File Servers
        • AS2
        • FTP
        • FTPS
        • HTTPS
        • SFTP
        • GoFast
      • Encryption
        • FIPS 140-2
        • Encrypted Folders
        • Open PGP
        • Open PGP for IBM i
        • GnuPG (GPG)
        • SSH
        • SSL/TLS
        • Zip with AES
        • Key Management
      • Agents
      • Translation
        • Delimited Text
        • Excel
        • Fixed Width Text
        • XML
      • Workflows
        • Resources
        • Tasks
      • Automation
        • Scheduler
        • File Monitoring
        • Commands and APIs
        • Triggers
      • Collaboration
        • GoDrive
        • Mobile Apps
        • Secure Mail
        • Secure Forms
        • Secure Folders
      • Auditing and Reporting
        • Auditing
        • Reporting
      • Clustering
      • Learn More
        • Detailed Features
        • Security
        • FAQs
        • Installation Requirements
        • Tutorials
          • Getting Started with GoAnywhere MFT
          • Getting Started with File Transfer Services
          • Getting Started with Projects
          • Automating Project Workflows
          • How to Encrypt Files with Open PGP
          • How to Import a CSV File into a Database
          • Getting Started with Domains
          • Converting Scripts to Projects
          • How to Read JSON Data and Insert it into a Database
          • How to Query a Database and Write the Data to JSON
          • Using the SQL Wizard to Join Data from Two Database Tables
          • How to Enable SSL for HTTPS/AS2 Server Connections
          • Configuring the AS2 Client to Send AS2 Messages
          • How to Configure the AS2 Service to Receive AS2 Transfers
          • Configuring Web User Accounts to Receive AS2 Messages
        • Pricing
        • Release Notes
    • GoAnywhere Gateway
      • How it Works
      • Load Balancing
      • PCI DSS Compliance
      • Installation Requirements
      • Release Notes
      • FAQs
      • Pricing
    • Free FTP Client
    • Free FTP Server
    • Free Collaboration
    • Free OpenPGP Encryption
      • Download
      • Documentation
        • Installation
          • Microsoft Windows
          • Linux and Unix
          • Mac OS X
        • Key Manager
        • Key Preferences
        • Encrypt
        • Sign
        • Encrypt and Sign
        • Decrypt and Verify
        • Verify
      • Automation
      • Support
  • Resources
    • Brochures
    • Case Studies
    • Compliance
    • Testimonials
    • Video Library
    • Video Reviews
    • Webinars
    • White Paper Library
  • Company
    • About Us
    • Blog
    • Certifications
    • Upcoming Events
    • News
    • Our Customers
    • Testimonials
  • Support
  • Request a Demo
  • Live Chat
  • Customer Login
  • Blog