根据txt文件更新表行 [英] updating table rows based on txt file

问题描述

我一直在搜索，但到目前为止，我仅发现了如何基于csv文件将日期插入表中。

I have been searching but so far I only found how to insert date into tables based on a csv files.

我有以下情况：

目录名称= ticketID

Directory name = ticketID

在此目录中，我有几个文件，例如：

Inside this directory I have a couple of files, like:

• Description.txt


• Summary.txt -包含票头并且已成功导入。


• Progress _＃。txt -这是每次票已过时。我得到一个新文件。


• Solution.txt

• Description.txt
• Summary.txt - Contains ticket header and has been imported succefully.
• Progress_#.txt - this is everytime a ticket gets udpdated. I get a new file.
• Solution.txt

导入 Issue.txt 很容易，因为它实际上是CSV。

Importing the Issue.txt was easy since this was actually a CSV.

现在，我的问题是描述和进度文件。

Now my problem is with Description and Progress files.

我需要使用此文件中的数据更新现有行。

I need to update the existing rows with the data from this files. Something on the line of

update table_ticket set table_ticket.description = Description.txt where ticket_number = directoryname

我使用的是PostgreSQL， COPY 命令对新数据有效，并且由于'，; /特殊字符，它仍然会失败。

I'm using PostgreSQL and the COPY command is valid for new data and it would still fail due to the ',;/ special chars.

我想使用bash脚本来执行此操作，但似乎不可能：

I wanted to do this using bash script, but it seem that it is it won't be possible:

for i in `find . -type d`
do
  update table_ticket 
  set table_ticket.description = $i/Description.txt
  where ticket_number = $i
done

当然，以上代码将考虑到数据库的连接。

Of course the above code would take into consideration connection to the database.

任何人都对如何使用shell脚本实现此想法有想法。还是最好用Java制作一些东西并读取并更新记录，尽管我想避免这种方法。

Anyone has a idea on how I could achieve this using shell script. Or would it be better to just make something in Java and read and update the record, although I would like to avoid this approach.

感谢
Alex p>

Thanks Alex

推荐答案

感谢您的回答，但我遇到了这个问题：

Thanks for the answer, but I came across this:

psql -U dbuser -h dbhost db 
\set content = `cat PATH/Description.txt`
update table_ticket set description = :'content' where ticketnr = TICKETNR;

将其放入一个简单的脚本中，我创建了以下内容：

Putting this into a simple script I created the following:

#!/bin/bash
for i in `find . -type d|grep ^./CS`
do
    p=`echo $i|cut -b3-12 -`
    echo $p
    sed s/PATH/${p}/g cmd.sql > cmd.tmp.sql
    ticketnr=`echo $p|cut -b5-10 -`
    sed -i s/TICKETNR/${ticketnr}/g cmd.tmp.sql
    cat cmd.tmp.sql
    psql -U supportAdmin -h localhost supportdb -f cmd.tmp.sql
done

缺点是它将始终创建一个新连接，稍后我将更改为创建单个文件

The downside is that it will create always a new connection, later I'll change to create a single file

但是它确实可以完成我的工作正在寻找，将内容放在单个列中。

But it does exactly what I was looking for, putting the contents inside a single column.

这篇关于根据txt文件更新表行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！