Alexandre Finger
|
| Posted: 11/10/2003, 8:43 AM |
|
I want to import a txt file (with ; as separator) into an editable grid
could somebody help me?
Thank Alex
|
|
|
 |
Ken Hardwick
|
| Posted: 11/10/2003, 5:05 PM |
|
Alex,
Would be glad to help but not sure where you need help.
Please explain further in what area you need help :
Like :
How to read a text file ?
How to work with an Editable grid ?
How to load the text file information into a grid ?
What version of CodeCharge and what format do you generate your code..ASP,PHP,etc.
|
|
|
|
Alexandre Finger
|
| Posted: 11/13/2003, 12:24 PM |
|
Ken,
I use Code Charge Studio 2,x, and ASP
Here is more detail :
My editable have columns with this name:
Entreprise - Employee Name - $/hrs - Date - Total Hours
fields of the editable grid have these names :
folio_id, salarie_id, taux_hres, date, TotalHours
and my text file have the same fields names with data, like:
folio_id, salarie_id, taux_hres, date, TotalHours
1001,2025,20.55,2003-11-11,37.5
1002,2026,22.35,2003-11-11,37.5
1003,2025,25.55,2003-11-11,37.5
I would like to put the information in each row of the editable grid because Time Manager have to accept or correct time before inserting in database.
Thank you for your help.
Alexandre
|
|
|
|
Ken Hardwick
|
| Posted: 11/14/2003, 3:16 AM |
|
Alex,
I would create a SQL Select statement with Unions from the Text file.
Time.txt =
folio_id;salarie_id;taux_hres;time_date;TotalHours
1001;2025;20.55;2003-11-11;37.5
1002;2026;22.35;2003-11-11;37.5
1003;2025;25.55;2003-11-11;37.5
Resulting SQL would be (
Select
1001 as "folio_id",
2025 as "salarie_id",
20.55 as "taux_hres",
2003-11-11 as "time_date",
37.5 as "TotalHours"
From Dual
Union
Select
1002 as "folio_id",
2026 as "salarie_id",
22.35 as "taux_hres",
2003-11-11 as "time_date",
37.5 as "TotalHours"
From Dual
Union
Select
1003 as "folio_id",
2025 as "salarie_id",
25.55 as "taux_hres",
2003-11-11 as "time_date",
37.5 as "TotalHours"
From Dual
Note I am using Dual - a dummy table in Oracle)
or
can use something like
Select fields... From tableName where rownum < 2
or
For Access
Select top 1 fields... from tableName
I am using ASP and CC.
1) Created a grid form and added the following in custom SQL
(by the way, "date" is an reserved word in Oracle so change field name to Time_Date)
Select 1 as folio_id,
2 as salarie_id,
3 as taux_hres,
2003-11-11 as Time_date,
5 as TotalHours
From Dual
This provides the CodeCharge form a database "table" to work with.
2) Added the following to the grid forms open event :
This opens the text file, reads it and creates a new SQL with unions
Then replaces sSQL with this new SQL created from the text file.
Set up constants
Const ForReading = 1
Const Create = False
' Declare local variables
'Dim objFSO
' FileSystemObject
'Dim TS
' TextStreamObject
'Dim strLine
' local variable to store Line
'Dim strFileName
' local variable to store fileName
strFileName = Server.MapPath("time.txt")
' Instantiate the FileSystemObject
Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
' use Opentextfile Method to Open the text File
Set TS = objFSO.OpenTextFile(strFileName, ForReading, Create)
quote = """"
quoteComma = ""","
FromUnion = " From Dual Union"
If Not TS.AtEndOfStream Then
cnt = 1
Do While Not TS.AtendOfStream
strLine = TS.ReadLine
if cnt = 1 then
'This is header line with Field names listed
FieldNames = Split(strLine,";")
'Fieldnames(0)---(4)
else
FieldValues = Split(strLine,";")
SQL_Line = " Select "
for Cnt2 = 0 to 3
SQL_Line = SQL_Line + FieldValues(cnt2) + " as " + quote + fieldnames(cnt2) + quotecomma
next
cnt2 = 4
SQL_Line = SQL_Line + FieldValues(cnt2) + " as " + quote + fieldnames(cnt2) + quote + FromUnion
end if
' Read one line at a time
' Response.Write strLine
' Display that line
' Response.Write "<br>"
' Remove UNION from last line
' Response.Write Sql_Line
' Response.Write "<br>"
SQL_Statement = SQL_Statement + " " + SQL_Line
cnt = cnt + 1
Loop
End If
' close TextStreamObject
' and destroy local variables to relase memory
TS.Close
Set TS = Nothing
Set objFSO = Nothing
SQL_Statement = Left(SQL_Statement ,len(SQL_Statement )-5)
' Response.Write "<br>"
' Response.Write SQL_Statement
sSQL = SQL_Statement
'end open event
This gets you a grid with data from the text file.
To save/update text file, you would now reverse the process and write to a file.
Hope this helps.
Ken Hardwick
Norman, Ok
Home of the Oklahoma Sooners
|
|
|
|
Ken Hardwick
|
| Posted: 11/14/2003, 3:38 AM |
|
Just wanted to give credit to DevASP for the code I used to read the text file.
See at :
http://www.devasp.com/Samples/writetofile.asp
|
|
|
|
Ken Hardwick
|
| Posted: 11/14/2003, 3:44 AM |
|
Opps...that was the wrong article :
http://www.devasp.com/Samples/readfromfile.asp
|
|
|
|
|
