+ Post New Thread
Results 1 to 4 of 4
MIS Systems Thread, SIMS Report and Command Line Report produce different files in Technical; Hi all, I have done a report within SIMS which exports out a file that is TAB delimited. However if ...
  1. #1
    Grazza's Avatar
    Join Date
    Jun 2010
    Location
    Walsall
    Posts
    34
    Thank Post
    2
    Thanked 4 Times in 1 Post
    Rep Power
    9

    SIMS Report and Command Line Report produce different files

    Hi all,

    I have done a report within SIMS which exports out a file that is TAB delimited.

    However if I use the command line reporter (which I need for my programme) it produces a CSV.

    Ideas please?

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,452
    Thank Post
    1,439
    Thanked 1,170 Times in 798 Posts
    Rep Power
    707
    I'm afraid you're stuck with it!

    What I have done in the past is write a small script to open the resulting CSV in Excel and save it as whatever format I need - also helps whereby SIMS is inconsistent on whether or not it encloses strings in quotes, which causes SQL havoc:

    Code:
    'Check arguments
    If WScript.Arguments.Length <> 1 Then
    	WScript.Echo "Usage: CScript CleanCSV Path/To/CSV"
    	WScript.Quit
    End If 
    
    Set myExcel = CreateObject("Excel.Application")
    myExcel.Visible = False
    myExcel.Application.DisplayAlerts = False
    
    Set myWorkbook = myExcel.Workbooks.Add()
    Set mySheet = myWorkbook.Sheets.Add()
    
    Dim arr(99)
    For x = 0 to 99
    	arr(x) = 2
    Next
    
    Set QT = mySheet.QueryTables.Add("TEXT;" + WSCript.Arguments(0), mySheet.Range("$A$1"))
    With QT
    	.TextFileCommaDelimiter = True
    	.TextFileColumnDataTypes = arr
    	.Refresh
    End With
    
    myWorkbook.SaveAs WScript.Arguments(0), 6
    myWorkbook.Close
    myExcel.Application.Quit
    The arr() part is a bit of a hack to make sure Excel doesn't reformat any dates etc. (some of our reg groups are a number followed by 'E' followed by another number which Excel interprets as scientific notation!)

  3. #3
    Grazza's Avatar
    Join Date
    Jun 2010
    Location
    Walsall
    Posts
    34
    Thank Post
    2
    Thanked 4 Times in 1 Post
    Rep Power
    9
    It's a pain I need it tab delimited as the text being exported may contains "," so throws my programme as I did decode the text looking for ","
    Why does it export CSV when the report is setup as a Tab Delimited report !
    Sort it our Capita !!!!

  4. #4

    Join Date
    Sep 2006
    Location
    London
    Posts
    1,326
    Thank Post
    36
    Thanked 353 Times in 238 Posts
    Rep Power
    79
    Please log a case.

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] SIMs reports and Macros in Excel
    By GillNash in forum MIS Systems
    Replies: 12
    Last Post: 6th December 2011, 12:00 PM
  2. [SIMS] SIMs and Command Reporter
    By RuAware in forum MIS Systems
    Replies: 43
    Last Post: 25th February 2011, 06:57 PM
  3. Replies: 1
    Last Post: 19th October 2010, 09:48 AM
  4. SIMS.net and the missing reports
    By shappaeye in forum MIS Systems
    Replies: 0
    Last Post: 12th June 2008, 09:25 AM
  5. urgent: Sims.net command line reporter (CLR)
    By russdev in forum MIS Systems
    Replies: 2
    Last Post: 20th September 2007, 02:00 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •